MySQL exports all indexes and constraints. mysql exports indexes.

MySQL exports all indexes and constraints. mysql exports indexes.

This article summarizes how MySQL exports all indexes and constraints for your convenience. The details are as follows:

1. Export the statement for creating an auto-increment field:

SELECTCONCAT('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.COLUMNSWHERE TABLE_SCHEMA = 'source_database_name' ANDEXTRA = UPPER('AUTO_INCREMENT')ORDER BY TABLE_NAME ASC

2. Export all indexes:

SELECTCONCAT('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 ', 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.STATISTICSWHERE TABLE_SCHEMA = 'pbq'GROUP BY TABLE_NAME, INDEX_NAMEORDER BY TABLE_NAME ASC, INDEX_NAME ASC

3. Create and delete all auto-increment fields:

SELECTCONCAT('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.COLUMNSWHERE TABLE_SCHEMA = 'destination_database_name' ANDEXTRA = UPPER('AUTO_INCREMENT')ORDER BY TABLE_NAME ASC

4. Delete All indexes in the database:

SELECTCONCAT('ALTER TABLE `',TABLE_NAME,'` ',GROUP_CONCAT(DISTINCTCONCAT('DROP ',IF(UPPER(INDEX_NAME) = 'PRIMARY','PRIMARY KEY',CONCAT('INDEX `', INDEX_NAME, '`')))SEPARATOR ', '),';')FROM information_schema.STATISTICSWHERE TABLE_SCHEMA = 'destination_database_name'GROUP BY TABLE_NAMEORDER BY TABLE_NAME ASC

I hope the examples described in this article will be helpful to you.

[MYSQL] different keys and indexes in MYSQL

A Key is a part of the relational model theory. For example, a Primary Key or a Foreign Key (Foreign)
Key), used for data integrity check and uniqueness constraints. The Index is at the implementation level. For example, you can create an Index on any column of the table. When the column to be indexed is in the Where condition of the SQL statement, you can quickly locate the data, to quickly search. As for Unique
Index is only one of the Index types. If Unique Index is set up, the data in this column cannot be duplicated.
You can further optimize indexes of the Index type.

Therefore, when designing a table, the Key is only at the model level. When you need to optimize the query, you can create an index for the relevant columns.

In addition, for a Primary Key column in MySQL, MySQL has automatically created a Unique Index for it without having to repeat the Index.

SQL statement for mysql to delete constraints!

Use the alter table statement:

Complete usage:
ALTER [IGNORE] TABLE tbl_name

Alter_specification [, alter_specification]...

Alter_specification:

Table_option...
| ADD [COLUMN] column_definition [FIRST | AFTER col_name]
| ADD [COLUMN] (column_definition ,...)
| ADD {INDEX | KEY} [index_name] [index_type] (index_col_name ,...)
| ADD [CONSTRAINT [symbol]

Primary key [index_type] (index_col_name ,...)
| ADD [CONSTRAINT [symbol]

UNIQUE [INDEX | KEY] [index_name] [index_type] (index_col_name ,...)
| ADD [FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (index_col_name ,...)
| ADD [CONSTRAINT [symbol]

Foreign key [index_name] (index_col_name ,...)

[Reference_definition]
| ALTER [COLUMN] col_name {set default literal | drop default}
| CHANGE [COLUMN] old_col_name column_definition

[FIRST | AFTER col_name]
| MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP {INDEX | KEY} index_name
| Drop foreign key fk_symbol
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO] new_tbl_name
| Order by col_name [, col_name]...
| Convert to character set charset_name [COLLATE collation_name]
| [DEFAULT] character set charset_name [COLLATE collation_name]
| DISCARD TABLESPACE
| IMPORT TABLESPACE

Index_col_name:

Col_name [(length)] [ASC | DESC]

Index_type:

USING {BTREE | HASH}

Delete the primary key and foreign key constraints:

Alter table mytablename
DROP PRIMARY KEY
Drop foreign key fk_sym ...... remaining full text>