摘要： MySQL批量生成建表语句

关键词： MySQL、大批量、挑选、建表语句

整体说明

在使用MySQL的时候，遇到需要在大批量的表中，挑选一部分表，权限又只有只读权限，工具又没有合适的，最终使用了MySQL后台表，生成建表语句，具体的做法，大致如下：

一、需求背景

1.1、需求特点

• 表数量大： 从如下截图可以看出，这个库总计369张表。

• 指定表列表： 从如下 EXCEL 列表可知，总计选择了 258张指定表，做数据集成，数据集成工具无法自动创建目的表。

1.2、权限限制

• 表只读权限： 由于公司要求，生产环境的数据库，只能给 只读 ，所以没法通过创建函数和存储过程的方式。
• 网络无法联通： 网络环境是生产的环境，本地没有办法直接写程序通过JDBC等方式，连接数据库，查询数据，只能通过 VPN + 堡垒机 连接，也没有上传部署程序的权限。
• 可以读系统 information_schema 库： 只读权限，可以读取系统库， information_schema 库 ，为我们后面的方案，提供的可能性。

二、方案选择

2.1、使用工具

2.1.1、Navicat 或者 DataGrip

这里以 DataGrip 举例，在这个功能来看，二者没有区别。

• 关键字筛选： 直接在左侧输入 accout 就能把表筛选出来，然后选中，右键生成SQL即可。

• 全量生成： 如果不筛选，直接点击生成，会全量生成所有建表语句

2.1.2、Dbeaver

关键字筛选 和 全量生成，这里就不再举例了，也支持。

• "过滤器"筛选： 依据下图，操作，配置过滤器，并把对应表名，添加进去，之后即可过滤表。
• 当我用这个工具时，我以为已经搞定了，结果这个过滤器添加那里只能一张表一张表添加，我这个 229张表，直接吐血，用不了！！

2.2、写SQL脚本

2.2.1、前置知识

前置知识，如下图简单介绍，如果需要再详细了解，可以查阅官方文档。

2.2.2、依赖系统表

依赖系统表简单列举如下图：

2.2.3、生成语句元素

2.2.4、建表语句准备

准备三张测试表，从简单到复杂，列举了建表语句中 90% 以上的情况

学生信息表，只包含基础元素

DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student` (
  `id` varchar(64) NOT NULL  COMMENT '数据标识',
  `create_user` varchar(200) DEFAULT NULL  COMMENT '创建人',
  `create_time` datetime DEFAULT NULL  COMMENT '创建时间',
  `update_user` varchar(200) DEFAULT NULL  COMMENT '更新人',
  `update_time` datetime DEFAULT NULL  COMMENT '更新时间',
  `student_id` varchar(200) DEFAULT NULL  COMMENT '学生id',
  `student_name` varchar(200) DEFAULT NULL  COMMENT '学生姓名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='学生信息表';

部门信息表，额外增加了索引的相关信息

DROP TABLE IF EXISTS `t_department`;
CREATE TABLE `t_department` (
  `id` varchar(64) NOT NULL  COMMENT '数据标识',
  `create_user` varchar(200) DEFAULT NULL  COMMENT '创建人',
  `create_time` datetime DEFAULT NULL  COMMENT '创建时间',
  `update_user` varchar(200) DEFAULT NULL  COMMENT '更新人',
  `update_time` datetime DEFAULT NULL  COMMENT '更新时间',
  `master_id` varchar(200) DEFAULT NULL  COMMENT '主表id',
  `status` int(11) DEFAULT NULL  COMMENT '删除状态 ',
  `department` varchar(200) NOT NULL  COMMENT '部门',
  `department_number` varchar(100) DEFAULT NULL  COMMENT '部门编号',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `status_department` (`department`,`status`),
  KEY `master_id` (`master_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='部门信息表';

学校信息表，额外增加了默认值等信息

DROP TABLE IF EXISTS `t_school`;
CREATE TABLE `t_school` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '数据标识',
  `create_user` varchar(200) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_user` varchar(200) DEFAULT NULL COMMENT '更新人',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `master_id` varchar(200) DEFAULT NULL COMMENT '主表id',
  `status` int(11) DEFAULT '0' COMMENT '删除状态',
  `status_name` varchar(20) DEFAULT '否' COMMENT '删除状态名称',
  `school` varchar(200) NOT NULL COMMENT '学校',
  `school_number` varchar(100) DEFAULT NULL COMMENT '学校编号',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='学校信息表';

2.2.5、SQL脚本

最终写完，脚本如下，每一行都加了注释，更加容易理解 （也让3周之后的我看的懂~）

SET SESSION group_concat_max_len = 102400; -- 增加拼接字符长度，防止不够长，导致结果被截断

SELECT CONCAT(
               'DROP TABLE IF EXISTS `', TABLE_NAME, '`;', CHAR(10), -- 删除表语句，可重复执行
               'CREATE TABLE `', TABLE_NAME, '` (', CHAR(10), -- `这个符号包裹表名，可避免关键字问题。 CHAR(10) 换行符，增加生成结果可读性
               GROUP_CONCAT('  ', '`', -- 分组拼接，空格增加可读性
                            COLUMN_NAME, '` ', COLUMN_TYPE, -- 字段名和字段类型
                            IF(IS_NULLABLE = 'NO', ' NOT NULL', ''), -- 字段是否可为空
                            IF(IS_NULLABLE = 'NO' AND COLUMN_DEFAULT IS NULL, '',
                               IF(COLUMN_DEFAULT IS NULL, ' DEFAULT NULL', -- 字段默认值，空值处理，避免影响其他字段拼接
                                  if(DATA_TYPE IN ('year', 'date', 'time', 'datetime', 'timestamp'),
                                     CONCAT(' DEFAULT ', COLUMN_DEFAULT),
                                     CONCAT(' DEFAULT \'', COLUMN_DEFAULT, '\'')) -- 时间类型是原值，其他类型需要加单引号
                                   )
                                ),
                            IF(EXTRA <> '', CONCAT(' ', UPPER(EXTRA)), ''), -- 额外的配置，转换为大写
                            IF(COLUMN_COMMENT IS NOT NULL, CONCAT(' COMMENT ''', COLUMN_COMMENT, ''''), '') -- 字段注释
                            ORDER BY ORDINAL_POSITION SEPARATOR ',\n' -- 字段按照原有排序方式排序，逗号换行符分隔增加可读性
                   ),
               IF(COLUMN_PRIMARY_KEY_CONCAT <> '', CONCAT(',', CHAR(10), '  PRIMARY KEY ', COLUMN_PRIMARY_KEY_CONCAT), ''), -- 拼接主键
               IF(COLUMN_UNIQUE_KEY_CONCAT <> '', CONCAT(',', CHAR(10), '  UNIQUE KEY ', COLUMN_UNIQUE_KEY_CONCAT),
                  ''), -- 拼接唯一索引
               IF(COLUMN_KEY_CONCAT <> '', CONCAT(',', CHAR(10), '  KEY ', COLUMN_KEY_CONCAT), '') -- 拼接普通索引
           , CHAR(10), -- 换行符增加可读性
               ') ENGINE=', ENGINE, -- 表引擎 InnoDB等，
               ' DEFAULT CHARSET=',
               IF(CHARACTER_SET_NAME = 'utf8', 'utf8mb3', CHARACTER_SET_NAME), -- 表默认字符集，utf8 就是 utf8mb3
               IF(TABLE_COMMENT IS NOT NULL, CONCAT(' COMMENT=''', TABLE_COMMENT, ''';'), ';'), CHAR(10), CHAR(10) -- 表注释
           ) AS create_table_sql
FROM (SELECT t1.TABLE_SCHEMA,
             t1.TABLE_NAME,
             t1.COLUMN_NAME,
             t1.COLUMN_TYPE,
             t1.COLUMN_KEY,
             t1.COLUMN_COMMENT,
             t1.ORDINAL_POSITION,
             t1.COLUMN_DEFAULT,
             t1.IS_NULLABLE,
             t1.DATA_TYPE,
             t1.EXTRA,
             t2.ENGINE,
             t2.TABLE_COLLATION,
             t4.CHARACTER_SET_NAME,
             t2.TABLE_COMMENT,
             t3.COLUMN_PRIMARY_KEY_CONCAT,
             t3.COLUMN_UNIQUE_KEY_CONCAT,
             t3.COLUMN_KEY_CONCAT
      FROM INFORMATION_SCHEMA.COLUMNS t1 -- 字段信息
               JOIN INFORMATION_SCHEMA.TABLES t2 -- 表信息
                    ON t1.TABLE_NAME = t2.TABLE_NAME
                        AND t1.TABLE_SCHEMA = t2.TABLE_SCHEMA
               LEFT JOIN (select TABLE_SCHEMA,
                            TABLE_NAME,
                            max(COLUMN_PRIMARY_KEY_CONCAT) as COLUMN_PRIMARY_KEY_CONCAT,
                            max(COLUMN_UNIQUE_KEY_CONCAT)  as COLUMN_UNIQUE_KEY_CONCAT,
                            max(COLUMN_KEY_CONCAT)         as COLUMN_KEY_CONCAT
                     from (select t1.TABLE_SCHEMA,
                                  t1.TABLE_NAME,
                                  IF(t2.CONSTRAINT_TYPE = 'PRIMARY KEY',
                                     CONCAT(' (', GROUP_CONCAT('`', t1.COLUMN_NAME, '`' ORDER BY t1.SEQ_IN_INDEX), ')'),
                                     '')                                             as COLUMN_PRIMARY_KEY_CONCAT,
                                  IF(t2.CONSTRAINT_TYPE = 'UNIQUE', CONCAT('`', t1.INDEX_NAME, '` (',
                                                                           GROUP_CONCAT('`', t1.COLUMN_NAME, '`' ORDER BY t1.SEQ_IN_INDEX),
                                                                           ')'), '') as COLUMN_UNIQUE_KEY_CONCAT,
                                  IF(t2.CONSTRAINT_NAME IS NULL, CONCAT('`', t1.INDEX_NAME, '` (',
                                                                        GROUP_CONCAT('`', t1.COLUMN_NAME, '`' ORDER BY t1.SEQ_IN_INDEX),
                                                                        ')'), '')    as COLUMN_KEY_CONCAT
                           FROM INFORMATION_SCHEMA.STATISTICS t1 -- 索引信息
                                    LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t2 -- 表约束信息
                                              ON t1.INDEX_NAME = t2.CONSTRAINT_NAME
                                                  AND t1.TABLE_SCHEMA = t2.TABLE_SCHEMA
                                                  AND t1.TABLE_NAME = t2.TABLE_NAME
                           WHERE 1 = 1
                             AND t1.TABLE_SCHEMA = 'shscnq_st' -- $$ 执行时需替换为实际数据库名
                           GROUP BY t1.TABLE_SCHEMA, t1.TABLE_NAME, t1.INDEX_NAME, t2.CONSTRAINT_TYPE) t1
                     GROUP BY TABLE_SCHEMA, TABLE_NAME) t3
                    ON t1.TABLE_SCHEMA = t3.TABLE_SCHEMA
                        AND t1.TABLE_NAME = t3.TABLE_NAME
               JOIN INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY t4 -- 排序规则和字符集关系信息
                    ON t2.TABLE_COLLATION = t4.COLLATION_NAME
      WHERE t1.TABLE_SCHEMA = 'shscnq_st' -- $$ 执行时替换为实际数据库名
        AND t1.TABLE_NAME in (
                              't_department',
                              't_student',
                              't_school'
          )                               -- $$ 执行时替换为实际表名列表，单引号包裹，逗号分隔
        AND t2.TABLE_TYPE = 'BASE TABLE' -- 基础表，不包含视图等
     ) AS tmp
GROUP BY TABLE_NAME, ENGINE, CHARACTER_SET_NAME, TABLE_COMMENT;

2.2.6、缺点说明

这个脚本只能生成包含前面语句元素的语句，如果包含了类似 分区、其他种类索引、在MySQL并不常用的元素，只能自行修改脚本了。

就像之前有个脱口秀演员说过 “重启能解决 90%的电脑问题，那剩余10%怎么办？哪有问题能够 100%解决的~”

2.2.7、效果展示

执行上面脚本，生成了三个建表语句，经过对比，和 使用 MySQL 自带的生成建表语句的一样。至于为什么不使用下面语句，因为这个只能单个表使用，无法大批量筛选使用。

show create table + 表名