获取MySQL 5.5表元信息
获取MySQL 5.5表元信息
最近，在写一个代码工具，从数据读取表生成实体Bean、SQLMap，需要获取到数据库的元信息，为了适应不同的数据库，需要针对每一种数据库实现一种获取元信息的方式，这里是MySQL5.5元信息的获取方式： 1、获取一个数据库下面所有的表 SELECT t.table_name,t.table_comment,t.create_time,FROM information_schema.tables t
WHERE t.table_schema = SCHEMA(); 2、获取一个表的元信息 SELECT t.column_name,
t.data_type,
CAST(SUBSTR(t.column_type, INSTR(t.column_type, '(') + 1, INSTR(t.column_type,')') - INSTR(t.column_type, '(') - 1) AS CHAR(20)) data_length,
CAST(t.column_type AS CHAR(20)) column_type,
t.column_comment,
IF (t.is_nullable='YES',1,0) is_nullable,
IF (t.column_key = 'PRI', 1, 0) is_key
FROM information_schema.columns t
WHERE t.table_schema = SCHEMA() AND
t.table_name = '表名'
ORDER BY t.ordinal_position; 3、获取一个表的建表语句 SHOW CREATE TABLE 表名; 或者另外一种方式： CREATE FUNCTION `get_tab_ddl`(p_tab_name VARCHAR(100)) RETURNS text
NO SQL
DETERMINISTIC
BEGIN
DECLARE v_return TEXT DEFAULT '';
DECLARE v_ddl TEXT DEFAULT '';
#表信息相关变量
DECLARE v_engine VARCHAR(255);
DECLARE v_row_format VARCHAR(255);
DECLARE v_tab_comment VARCHAR(255);
#约束相关变量
DECLARE v_cons_name VARCHAR(255);
DECLARE v_cons_type VARCHAR(255);
#约束列相关变量
DECLARE v_cons_col_name VARCHAR(255);
DECLARE v_ref_tab_schema VARCHAR(255);
DECLARE v_ref_tab_name VARCHAR(255);
DECLARE v_ref_col_name VARCHAR(255);
DECLARE v_update_rule VARCHAR(255);
DECLARE v_delete_rule VARCHAR(255);
#索引相关变更
DECLARE v_index_name VARCHAR(255);
DECLARE v_l_index_name VARCHAR(255) DEFAULT '';
DECLARE v_ind_col_name VARCHAR(255);
DECLARE v_done INT DEFAULT 0;
#列游标
DECLARE cur_column CURSOR FOR
SELECT CONCAT(' ','`',t.column_name,'` ',column_type,
IF(t.is_nullable = 'NO',' NOT NULL',''),
IF(t.extra IS NULL,'',CONCAT(' ',t.extra)),
IF(t.column_default IS NULL,'',CONCAT(' ','DEFAULT ' , "'" , t.column_default , "'")),
IF(t.column_comment = '','',CONCAT(' ','COMMENT ' , "'" , t.column_comment , "'")),',') tab_column
FROM information_schema.columns t
WHERE t.table_schema = SCHEMA()
AND t.table_name = p_tab_name
ORDER BY t.ordinal_position;
#主键约束
DECLARE cur_pk CURSOR FOR
SELECT t.column_name
FROM information_schema.key_column_usage t
WHERE t.table_schema = SCHEMA()
AND t.table_name = p_tab_name
AND t.constraint_name = 'PRIMARY'
ORDER BY t.ordinal_position;
#其它约束游标
DECLARE cur_cons CURSOR FOR
SELECT t.constraint_type,t.constraint_name
FROM information_schema.table_constraints t
WHERE t.table_schema = SCHEMA()
AND t.table_name = p_tab_name
AND t.constraint_type <> 'PRIMARY KEY';
#约束列游标
DECLARE cur_col_cons CURSOR FOR
SELECT t.column_name,t.referenced_table_schema,t.referenced_table_name,t.referenced_column_name,c.update_rule,c.delete_rule
FROM information_schema.key_column_usage t
LEFT JOIN information_schema.referential_constraints c ON (t.table_name = c.table_name AND t.constraint_name = c.constraint_name)
WHERE t.table_schema = SCHEMA()
AND t.table_name = p_tab_name
AND t.constraint_name = v_cons_name
ORDER BY t.ordinal_position;
#表上索引游标
DECLARE cur_index CURSOR FOR
SELECT t.index_name,t.column_name
FROM information_schema.statistics t
WHERE t.table_schema = SCHEMA()
AND t.table_name = p_tab_name
AND NOT EXISTS (SELECT 1 FROM information_schema.table_constraints c
WHERE t.table_schema = c.table_schema
AND t.table_name = c.table_name
AND t.index_name = c.constraint_name)
ORDER BY t.index_name,t.seq_in_index;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET v_done=1;
#表信息
SELECT IF(t.engine = '' OR t.engine IS NULL,'',CONCAT(' ENGINE=',t.engine)) ENGINE,
t.row_format,
IF(t.table_comment = '' OR t.table_comment IS NULL,'',CONCAT(" COMMENT='",t.table_comment,"'")) table_comment
INTO v_engine,v_row_format,v_tab_comment
FROM information_schema.tables t
WHERE t.table_schema = SCHEMA()
AND t.table_name = p_tab_name;
SET v_return = CONCAT('CREATE TABLE `',p_tab_name,'` (',CHAR(13));
#打开列游标
OPEN cur_column;
FETCH cur_column INTO v_ddl;
WHILE v_done <> 1 DO
SET v_return = CONCAT(v_return,v_ddl,CHAR(13));
FETCH cur_column INTO v_ddl;
END WHILE;
CLOSE cur_column;
SET v_ddl = '';
#打开主键约束
SET v_done = 0;
OPEN cur_pk ;
FETCH cur_pk INTO v_cons_col_name;
WHILE v_done <> 1 DO
SET v_ddl = CONCAT(v_ddl,'`',v_cons_col_name,'`,');
FETCH cur_pk INTO v_cons_col_name;
END WHILE;
CLOSE cur_pk;
IF v_ddl <> '' THEN
SET v_return = CONCAT(v_return,' ','PRIMARY KEY (',LEFT(v_ddl,CHAR_LENGTH(v_ddl) - 1),'),',CHAR(13));
END IF;
SET v_return = CONCAT(LEFT(v_return,CHAR_LENGTH(v_return) - 2),CHAR(13));
SET v_return = CONCAT(v_return,') ',v_engine,v_tab_comment,' ;',CHAR(13));
#打开其它约束游标
SET v_done = 0;
OPEN cur_cons;
FETCH cur_cons INTO v_cons_type,v_cons_name;
WHILE v_done <> 1 DO
IF v_cons_type = 'FOREIGN KEY' THEN
SET v_return = CONCAT(v_return,CHAR(13),'ALTER TABLE `',p_tab_name,'` ADD CONSTRAINT `',v_cons_name,'` FOREIGN KEY (');
#打开外键约束列游标
OPEN cur_col_cons;
FETCH cur_col_cons INTO v_cons_col_name,v_ref_tab_schema,v_ref_tab_name,v_ref_col_name,v_update_rule ,v_delete_rule;
WHILE v_done <> 1 DO
SET v_return = CONCAT(v_return,'`',v_cons_col_name,'`) REFERENCES `',v_ref_tab_name,'` (`',v_ref_col_name,'`) ',
'ON DELETE ',v_delete_rule,' ON UPDATE ',v_update_rule);
FETCH cur_col_cons INTO v_cons_col_name,v_ref_tab_schema,v_ref_tab_name,v_ref_col_name,v_update_rule ,v_delete_rule;
END WHILE;
CLOSE cur_col_cons;
SET v_return = CONCAT(v_return,';',CHAR(13));
ELSE
SET v_return = CONCAT(v_return,CHAR(13),'ALTER TABLE `',p_tab_name,'` ADD CONSTRAINT `',v_cons_name,'` UNQINE (');
#打开唯一约束列游标
OPEN cur_col_cons;
FETCH cur_col_cons INTO v_cons_col_name,v_ref_tab_schema,v_ref_tab_name,v_ref_col_name,v_update_rule ,v_delete_rule;
WHILE v_done <> 1 DO
SET v_return = CONCAT(v_return,'`',v_cons_col_name,'`,');
FETCH cur_col_cons INTO v_cons_col_name,v_ref_tab_schema,v_ref_tab_name,v_ref_col_name,v_update_rule ,v_delete_rule;
END WHILE;
CLOSE cur_col_cons;
SET v_return = CONCAT(LEFT(v_return,CHAR_LENGTH(v_return) - 1),');',CHAR(13));
END IF;
SET v_done = 0;
FETCH cur_cons INTO v_cons_type,v_cons_name;
END WHILE;
CLOSE cur_cons;
#打开索引游标
SET v_done = 0;
SET v_ddl = '';
OPEN cur_index;
FETCH cur_index INTO v_index_name,v_ind_col_name;
WHILE v_done <> 1 DO
IF v_index_name = v_l_index_name THEN
SET v_ddl = CONCAT(v_ddl,'`',v_ind_col_name,'`,');
ELSEIF v_l_index_name IS NULL OR v_l_index_name = '' THEN
SET v_ddl = CONCAT(v_ddl,CHAR(13),'CREATE INDEX `',v_index_name,'` ON `',p_tab_name,'` (`',v_ind_col_name,'`,');
ELSE
SET v_ddl = CONCAT(LEFT(v_ddl,CHAR_LENGTH(v_ddl) - 1),');',CHAR(13),CHAR(13),'CREATE INDEX `',
v_index_name,'` ON `',p_tab_name,'` (`',v_ind_col_name,'`,');
END IF;
SET v_l_index_name = v_index_name;
FETCH cur_index INTO v_index_name,v_ind_col_name;
END WHILE;
CLOSE cur_index;
IF v_ddl <> '' THEN
SET v_return = CONCAT(v_return,LEFT(v_ddl,CHAR_LENGTH(v_ddl) - 1),');',CHAR(13));
END IF;
RETURN v_return;
END
评论暂时关闭