Mysql export all index and constraint methods _mysql

This article summarizes the MySQL export all index and constraints of the method, provided to everyone to facilitate the query use. Specifically as follows:

1. Export Create a self-added field statement:

SELECT
CONCAT (
' ALTER TABLE ',
table_name, ', ',
' MODIFY COLUMN ',
column_name,
',
IF (UPPER (data_type) = ' INT ',
REPLACE (
substring_index (
UPPER (column_type),
') ',
1
),
' INT ',
' INTEGER '
,
UPPER (column_type)
,
') UNSIGNED not NULL auto_ INCREMENT; '
)
From INFORMATION_SCHEMA. COLUMNS
WHERE table_schema = ' source_database_name ' and
EXTRA = UPPER (' auto_increment ') Order by
Table_ NAME ASC

2. Export All indexes:

SELECT
CONCAT (' ALTER TABLE ', table_name, ', ', ' ADD ', 
 IF (non_unique = 1, Case
 UPPER (index_type) when
 ' Fulltext ' THEN ' fulltext index ' when
 ' spatial ' THEN ' spatial index '
 ELSE CONCAT (' INDEX ',
  index_name,
  ' Using ',
  Index_type end
,
IF (UPPER (index_name) = ' PRIMARY ',
 CONCAT (' PRIMARY KEY USING ', C14/>index_type
 ),
CONCAT (' UNIQUE INDEX ',
 index_name,
 ' USING ',
 index_type
)
)
, ' (', Group_concat (DISTINCT CONCAT (', column_name, ' ') Order by Seq_in_index ASC SEPARATOR ', '), '); As ' show_add_indexes ' from
information_schema. STATISTICS
WHERE table_schema = ' PBq '
GROUP by table_name, index_name order
by table_name ASC, index_name Asc

3. Create Delete all self-added fields:

SELECT
CONCAT (
' ALTER TABLE ',
table_name, ', ',
' MODIFY COLUMN ',
column_name,
',
IF (UPPER (data_type) = ' INT ',
REPLACE (
substring_index (
UPPER (column_type),
') ',
1
),
' INT ',
' INTEGER '
,
UPPER (column_type)
,
') UNSIGNED not NULL; '
)
From INFORMATION_SCHEMA. COLUMNS
WHERE table_schema = ' destination_database_name ' and
EXTRA = UPPER (' auto_increment ') Order by
TABLE_NAME ASC

4. Delete all indexes for the library:

SELECT
CONCAT (
' ALTER TABLE ',
table_name,
', ',
group_concat (
DISTINCT
CONCAT (
' DROP ',
IF (UPPER (index_name) = ' PRIMARY ',
' PRIMARY KEY ', CONCAT
(' INDEX ', index_name, ' "))
SEPARATOR ', ')
, '
;
') From INFORMATION_SCHEMA. STATISTICS
WHERE table_schema = ' destination_database_name '
GROUP by table_name ORDER BY
TABLE_NAME ASC

I hope the examples described in this article will help you.