导出外键:
SELECT
CONCAT (
'ALTER TABLE ',
c.`TABLE_SCHEMA`,
'.',
c.TABLE_NAME,
' add CONSTRAINT `',
c.CONSTRAINT_NAME,
'` FOREIGN KEY (',
b.`COLUMN_NAME`,
')REFERENCES ',
c.`TABLE_SCHEMA`,
'.',
b.`REFERENCED_TABLE_NAME`,
'(',
b.`REFERENCED_COLUMN_NAME`,
');'
)
FROM
information_schema.TABLE_CONSTRAINTS c,
information_schema.`KEY_COLUMN_USAGE` b
WHERE c.TABLE_SCHEMA = '数据库名称'
AND c.CONSTRAINT_TYPE = 'FOREIGN KEY'
AND c.`CONSTRAINT_NAME` = b.`CONSTRAINT_NAME`;
导出外键:
SELECT
CONCAT (
'create index ',
index_name,
' on ',
table_name,
'(',
column_name,
')',
' using ',
index_type,
';'
)
FROM
information_schema.`STATISTICS`
WHERE index_name != 'PRIMARY'
AND table_schema = '数据库名称';