如何为MySQL中的数据库中的所有表生成DDL

mysql -u 用户名 -p

USE 数据库名;

SELECT CONCAT('CREATE TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` (', GROUP_CONCAT(COLUMN_DDL ORDER BY ORDINAL_POSITION SEPARATOR ','), ');') AS DDL FROM (SELECT TABLE_SCHEMA, TABLE_NAME, CONCAT(COLUMN_NAME, ' ', COLUMN_TYPE, IF(IS_NULLABLE='NO', ' NOT NULL', ''), IF(COLUMN_DEFAULT IS NOT NULL, CONCAT(' DEFAULT ', COLUMN_DEFAULT), ''), IF(COLUMN_KEY='PRI', ' PRIMARY KEY', ''), IF(COLUMN_KEY='UNI', ' UNIQUE KEY', ''), IF(EXTRA='auto_increment', ' AUTO_INCREMENT', '')) AS COLUMN_DDL, ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 数据库名 ORDER BY TABLE_NAME, ORDINAL_POSITION) AS COLUMNS_DDL GROUP BY TABLE_NAME;

SELECT CONCAT('CREATE TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` (', GROUP_CONCAT(COLUMN_DDL ORDER BY ORDINAL_POSITION SEPARATOR ','), ');') AS DDL FROM (SELECT TABLE_SCHEMA, TABLE_NAME, CONCAT(COLUMN_NAME, ' ', COLUMN_TYPE, IF(IS_NULLABLE='NO', ' NOT NULL', ''), IF(COLUMN_DEFAULT IS NOT NULL, CONCAT(' DEFAULT ', COLUMN_DEFAULT), ''), IF(COLUMN_KEY='PRI', ' PRIMARY KEY', ''), IF(COLUMN_KEY='UNI', ' UNIQUE KEY', ''), IF(EXTRA='auto_increment', ' AUTO_INCREMENT', '')) AS COLUMN_DDL, ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 数据库名 ORDER BY TABLE_NAME, ORDINAL_POSITION) AS COLUMNS_DDL GROUP BY TABLE_NAME INTO OUTFILE 'ddl.sql';