Mysq导出数据库索引和外键

导出外键：

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 = '数据库名称';