网站首页 > 技术文章正文

MySQL批量生成建表语句

nanyue 2025-04-29 02:56:46 技术文章 2 ℃

摘要： 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 + 表名

上一篇： SQL 语句大全
下一篇：详解MySQL|一招实现从Oracle到MySQL的平滑数据迁移-爱可生

网站首页 > 技术文章 正文