获取MySQL5.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)

CHAR(20)) data_length,

CAST(t.column_type

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

NULL,

'',CONCAT(

' ',t.extra)),

IF(t.column_default

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

NULL,

'',CONCAT(

' ENGINE=',t.engine)) ENGINE,

t.row_format,

IF(t.table_comment =

OR t.table_comment

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

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),

'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

本文转自 leizhimin 51CTO博客，原文链接：http://blog.51cto.com/lavasoft/746471，如需转载请自行联系原作者