mysql提取建表语句_获取MySQL中某个数据库下所有表建表语句的DDL

本脚本可以获取MySQL中某个数据库下所有表建表语句的DDL

注意：本脚本需在MySQL8.0以上版本运行

注意：本脚本不获取VIEW创建语句

注意：本脚本没有处理主键，外键等约束

注意：本脚本只处理非分区表，分区表请自己改写脚本

注意：如果你做数据迁移，想把MySQL数据库迁移到其他数据库，自己改写脚本

WITH tab AS

(SELECT table_name FROM information_schema.TABLES WHERE table_schema = '数据库名' and table_type='BASE TABLE')

SELECT CASE

WHEN id = -1 THEN

concat('create table ', table_name)

WHEN id = 0 THEN

'('

WHEN id = 1 AND max_id = 1 THEN

concat(' ', column_name, ' ', column_type)

WHEN id = 1 AND max_id <> 1 THEN

concat(' ', column_name, ' ', column_type, ',')

WHEN id >= 1 AND id < max_id THEN

concat(' ', column_name, ' ', column_type, ',')

WHEN id = max_id THEN

concat(' ', column_name, ' ', column_type)

WHEN id = 999 THEN

');'

END create_table_ddl

FROM (SELECT table_name,

ordinal_position id,

max(ordinal_position) over(PARTITION BY table_schema, table_name) max_id,

column_name,

concat(column_type,

CASE

WHEN is_nullable = 'NO' THEN

' not null'

ELSE

''

END,

CASE

WHEN COLUMN_DEFAULT IS NULL THEN

''

WHEN COLUMN_DEFAULT IS NOT NULL AND

data_type IN ('varchar', 'char') THEN

concat(' default ', '''', column_default, '''')

WHEN COLUMN_DEFAULT IS NOT NULL AND

data_type NOT IN ('varchar', 'char') THEN

concat(' default ', column_default)

END,

'',

REPLACE(REPLACE(extra, 'DEFAULT_GENERATED', ''),

'auto_increment',

'')) column_type

FROM information_schema.COLUMNS

WHERE table_schema = '数据库名' and table_name in (select table_name from tab)

UNION ALL

SELECT table_name, -1 ordinal_position, NULL, NULL, NULL

FROM tab

UNION ALL

SELECT table_name, 0 ordinal_position, NULL, NULL, NULL

FROM tab

UNION ALL

SELECT table_name, 999 ordinal_position, NULL, NULL, NULL

FROM tab

ORDER BY table_name, id) a;
————————————————
版权声明：本文为CSDN博主「南都有雪」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_33220360/article/details/113216501