储存过程实现show create table
实现思路
- 首先，我们声明了一些变量，用于存储字段名、字段类型、索引信息等。
- 然后，我们使用游标遍历字段信息，通过查询 INFORMATION_SCHEMA.COLUMNS 表获取字段的详细信息。在遍历过程中，我们根据字段的属性构建字段定义部分，并添加注释。
- 接着，我们使用游标遍历索引信息，通过查询 INFORMATION_SCHEMA.STATISTICS 表获取索引的详细信息。在遍历过程中，我们构建索引定义部分。
- 在构建完字段和索引部分后，我们查询表的注释、引擎和字符集信息，并开始构建创建表的 SQL 语句。
- 如果表有主键，则将主键信息添加到创建表的 SQL 语句中。
- 最后，我们移除最后一个逗号和换行符，并根据表的注释、引擎和字符集构建完整的创建表的 SQL 语句。
通过调用这个存储过程，我们可以根据数据库名和表名自动生成创建表的 SQL 语句。只需要传入数据库名和表名，存储过程就会返回创建表的 SQL 语句。
完整储存过程
CREATE DEFINER=`root`@`%` PROCEDURE `generate_create_table`(IN db_name VARCHAR(100), IN tbl_name VARCHAR(100),out create_sql VARCHAR(5000))
BEGIN
-- 声明变量
DECLARE col_name VARCHAR(100); -- 字段名称
DECLARE col_type VARCHAR(100); -- 字段类型
DECLARE col_index VARCHAR(500); -- 索引信息
DECLARE col_nullable VARCHAR(50); -- 字段是否可为空
DECLARE col_comment VARCHAR(255); -- 字段注释
DECLARE col_default VARCHAR(50); -- 字段默认值
DECLARE col_extra VARCHAR(50); -- 额外属性
DECLARE done INT DEFAULT FALSE; -- 游标结束标志
DECLARE create_table_sql TEXT DEFAULT ''; -- 存储创建表的SQL语句
-- 使用游标遍历字段信息，构建字段部分
DECLARE cur CURSOR FOR SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_COMMENT, COLUMN_DEFAULT, EXTRA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = db_name AND TABLE_NAME = tbl_name;
-- 使用游标遍历索引信息，构建索引部分
DECLARE cur2 CURSOR FOR SELECT CONCAT( IF( NON_UNIQUE = 0, 'UNIQUE', IF( INDEX_TYPE LIKE 'FULLTEXT%', 'FULLTEXT KEY', 'KEY' ) ), ' `', INDEX_NAME, '` (', GROUP_CONCAT('`', COLUMN_NAME, '`'), ')', IF( INDEX_TYPE NOT LIKE 'FULLTEXT%', CONCAT(' USING ', INDEX_TYPE), '' ) ) AS INDEX_INFO FROM INFORMATION_SCHEMA.STATISTICS WHERE INDEX_NAME != 'PRIMARY' and TABLE_SCHEMA = db_name AND TABLE_NAME = tbl_name GROUP BY INDEX_NAME, INDEX_TYPE;
-- 设置游标结束处理程序
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 开始构建创建表的SQL语句(A完整创建SQL)
SET @sql = CONCAT('CREATE TABLE IF NOT EXISTS `', db_name, '`.`', tbl_name, '` (');
OPEN cur;
-- 检查并设置默认值和额外属性
read_loop: LOOP
FETCH cur INTO col_name, col_type, col_nullable, col_comment, col_default, col_extra;
IF done THEN
LEAVE read_loop;
END IF;
IF col_nullable = 'YES' THEN
SET col_nullable = 'DEFAULT NULL';
ELSE
SET col_nullable = 'NOT NULL';
END IF;
IF col_comment IS NOT NULL and col_comment!='' THEN
SET col_comment = CONCAT(' COMMENT ', QUOTE(col_comment));
ELSE
SET col_comment = '';
END IF;
IF col_default IS NOT NULL THEN
SET col_nullable = 'DEFAULT ';
SET col_default = col_default;
ELSE
SET col_default = '';
END IF;
-- id 去掉自增
IF col_extra IS NOT NULL and col_name != 'id' THEN
SET col_extra = CONCAT(col_extra, ' ');
ELSE
SET col_extra = '';
END IF;
-- 数据库编码可能导致col_comment字段异常，如果异常删除掉
-- 包含字段注释
SET create_table_sql = CONCAT(create_table_sql, '`', col_name, '` ', col_type, ' ', col_nullable, col_default, ' ', col_extra, col_comment, ',');
-- 不包含字段注释
-- SET create_table_sql = CONCAT(create_table_sql, '`', col_name, '` ', col_type, ' ', col_nullable, col_default, ' ', col_extra ,',');
END LOOP;
CLOSE cur;
SET create_table_sql = CONCAT(create_table_sql,'KEY `idx_id` (`id`) USING BTREE,');
OPEN cur2;
SET done = 0;
read_loop2: LOOP
FETCH cur2 INTO col_index;
IF done THEN
LEAVE read_loop2;
END IF;
SET create_table_sql = CONCAT(create_table_sql, col_index,',');
END LOOP;
CLOSE cur2;
-- 移除最后一个逗号和空格
SET create_table_sql = TRIM(TRAILING ',' FROM create_table_sql);
SET create_table_sql = CONCAT(@sql, create_table_sql, ') ENGINE=InnoDB DEFAULT CHARSET=utf8;');
-- 返回创建表的SQL语句
SET create_sql = create_table_sql;
END