MySQL导出所有Index和约束的方法，mysql导出index

MySQL导出所有Index和约束的方法，mysql导出index

本文汇总了MySQL导出所有Index 和 约束的方法，提供给大家以方便大家查询使用。具体如下：

1. 导出创建自增字段语句：

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. 导出所有索引：

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 ',
 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. 创建删除所有自增字段：

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. 删除库所有索引：

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

希望本文所述示例能够对大家有所帮助。

Key即键值，是关系模型理论中的一部份，比如有主键（Primary Key)，外键（Foreign
Key）等，用于数据完整性检否与唯一性约束等。而Index则处于实现层面，比如可以对表个的任意列建立索引，那么当建立索引的列处于SQL语句中的Where条件中时，就可以得到快速的数据定位，从而快速检索。至于Unique
Index，则只是属于Index中的一种而已，建立了Unique Index表示此列数据不可重复，猜想MySQL对Unique
Index类型的索引可以做进一步特殊优化吧。

于是乎，在设计表的时候，Key只是要处于模型层面的，而当需要进行查询优化，则对相关列建立索引即可。

另外，在MySQL中，对于一个Primary Key的列，MySQL已经自动对其建立了Unique Index，无需重复再在上面建立索引了。
 

用alter table语句：

完整用法：
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}

删除 主键 和外键约束:

ALTER TABLE mytablename
DROP PRIMARY KEY
DROP FOREIGN KEY fk_sym......余下全文>>