mysql 导出表注释_一次性获取mysql所有表注释及字段注释，并按格式导出

####获取所有表及注释

CREATE PROCEDURE `getTableColumns`()

BEGIN

DECLARE   _table_name ,_table_comment,_column_name ,_column_type,_column_comment  varchar(255) DEFAULT '';

DECLARE i int DEFAULT 1;

DECLARE   _fl   INT  DEFAULT 0;

DECLARE cursor_fl CURSOR  FOR  select table_name,table_comment from information_schema.tables where table_schema='consumer_sit';

DECLARE continue HANDLER for not found set _fl= 1;

TRUNCATE table temp_table_column;

OPEN cursor_fl;

#启动

fetch  cursor_fl INTO _table_name ,_table_comment;

WHILE _fl<> 1  do

INSERT INTO temp_table_column(`column_name` ,`column_type`) VALUES(i ,_table_name);

INSERT INTO temp_table_column(`column_name` ,`column_type`) VALUES('描述' ,_table_comment);

INSERT INTO temp_table_column(`column_name` ,`column_type`,`column_comment`) VALUES('字段' ,'字段类型','备注');

call findComment(_table_name,_table_comment);

INSERT INTO temp_table_column(`table_name` ,`table_comment`,`column_name` ,`column_type`,`column_comment` ) VALUES('' ,'','','','');

fetch  cursor_fl  INTO _table_name ,_table_comment;

SET i=i+1;

commit;

END WHILE;

CLOSE cursor_fl;

SELECT * FROM temp_table_column    ;

END

####获取字段注释

CREATE  PROCEDURE `findComment`(IN _table_name varchar(50),_table_comment varchar(255))

BEGIN

DECLARE   _column_name ,_column_type,_column_comment  varchar(255) DEFAULT '';

DECLARE   _fl   INT  DEFAULT 0;

DECLARE cursor_fl CURSOR  FOR SELECT   COLUMN_NAME ,COLUMN_TYPE ,COLUMN_COMMENT  FROM INFORMATION_SCHEMA.COLUMNS  WHERE   table_schema ='consumer_sit'  AND  table_name =_table_name;

DECLARE continue HANDLER for not found set _fl= 1;

OPEN cursor_fl;

#启动

fetch  cursor_fl INTO  _column_name ,_column_type,_column_comment;

WHILE _fl<> 1  do

INSERT INTO temp_table_column(`table_name` ,`table_comment`,`column_name` ,`column_type`,`column_comment` ) VALUES(_table_name ,_table_comment,_column_name , _column_type,_column_comment);

fetch  cursor_fl  INTO  _column_name ,_column_type,_column_comment;

commit;

END WHILE;

CLOSE cursor_fl;

END

#临时表

CREATE TABLE temp_table_column  (

`id` int(11) NOT NULL AUTO_INCREMENT,

`table_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

`table_comment` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

`column_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

`column_type` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

`column_comment` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 2125 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

##查询结果

select column_name 字段,column_type 字段类型 ,column_comment 备注 from temp_table_column；