从mysql元数据表中通过sql构造还原生成建表语句.pdfVIP

从mysql元数据表中通过sql构造还原⽣成建表语句 从mysql元数据表中通过sql 构造/还原/⽣成 建表语句 set @schema : zabbix; SELECT d.sql_script FROM (SELECT c1.* FROM (SELECT 1 typ_seq, table_name, CONCAT(create table , UPPER(table_name), () sql_script, 0 inner_seq FROM information_schema.tables WHERE `TABLE_SCHEMA` @schema UNION ALL SELECT 2, table_name, sql_script, inner_seq FROM (SELECT table_name, CONCAT(IF(a.`ORDINAL_POSITION` 1, , ,), , UPPER(column_name), , COLUMN_TYPE, CASE WHEN COLUMN_DEFAULT IS NULL OR TRIM(COLUMN_DEFAULT) THEN ELSE CONCAT( DEFAULT , CASE WHEN data_type IN (bigint , int, decimal, tinyint, float, smallint, date, double, time,datetime) THEN COLUMN_DEFAULT ELSE CONCAT(\, COLUMN_DEFAULT, \) END) END, IF(IS_NULLABLE NO, , ), extra ) sql_script, ordinal_position inner_seq FROM information_schema.columns a WHERE a.`TABLE_SCHEMA` @schema ORDER BY TABLE_NAME , ORDINAL_POSITION) t2 UNION ALL SELECT 3, tc.TABLE_NAME, CONCAT(,, tc.CONSTRAINT_TYPE, (, GROUP_CONCAT(CONCAT(, UPPER(kcu.`COLUMN_NAME`), ) ORDER BY kcu.`ORDINAL_POSITION` SEPARATOR ,), )), CASE tc.CONSTRAINT_TYPE WHEN PRIMARY KEY THEN 1 ELSE 3 END FROM information_schema.TABLE_CONSTRAINTS tc, information_schema.key_column_usage kcu WHERE tc.`TABLE_SCHEMA` @schema tc.`TABLE_SCHEMA` @schema AND kcu.`TABLE_SCHEMA` @schema AND kcu.`TABLE_SCHEMA`