网站首页 > 教程文章 正文
摘要： 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 + 表名猜你喜欢
- 2025-05-02 《JDBC》第14节:JDBC之获取数据库中的表信息和表字段信息
- 2025-05-02 mysql数据库ORDER BY优化总结(为排序使用索引)
- 2025-05-02 无法获取新增ID值问题排查（无法获取iccid什么意思）
- 2025-05-02 MySQL实现字段分割(一行转多行)（mysql 一行变多行）
- 2025-05-02 Python mysql批量更新数据(兼容动态数据库字段、表名)
- 2025-05-02 MySQL数据库中，数据量越来越大，有什么具体的优化方案么?
- 2025-05-02 Mysql中通过关联update将一张表的一个字段更新到另外一张表中
- 2025-05-02 MySQL多行数据合并为一个字段的方法
- 最近发表
- 标签列表
-
- location.href (44)
- document.ready (36)
- git checkout -b (34)
- 跃点数 (35)
- 阿里云镜像地址 (33)
- qt qmessagebox (36)
- md5 sha1 (32)
- mybatis plus page (35)
- semaphore 使用详解 (32)
- update from 语句 (32)
- vue @scroll (38)
- 堆栈区别 (33)
- 在线子域名爆破 (32)
- 什么是容器 (33)
- sha1 md5 (33)
- navicat导出数据 (34)
- 阿里云acp考试 (33)
- 阿里云 nacos (34)
- redhat官网下载镜像 (36)
- srs服务器 (33)
- pico开发者 (33)
- https的端口号 (34)
- vscode更改主题 (35)
- 阿里云资源池 (34)
- os.path.join (33)