Mysql中索引和约束的示例语句
外键
查询一个表的主键是哪些表的外键
SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA='mydbname' ANDREFERENCED_TABLE_NAME='表名';
导出所有外键语句
SELECT
CONCAT('ALTERTABLE',TABLE_NAME,'ADDCONSTRAINT',CONSTRAINT_NAME,'FOREIGNKEY(',COLUMN_NAME,')REFERENCES',REFERENCED_TABLE_NAME,'(',REFERENCED_COLUMN_NAME,')ONDELETECASCADEONUPDATECASCADE;')
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
TABLE_SCHEMA='mydbname'
ANDREFERENCED_TABLE_NAMEISNOTNULL;
删除所有外键语句
SELECT
CONCAT('ALTERTABLE',TABLE_NAME,'DROPFOREIGNKEY',CONSTRAINT_NAME,';')
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
TABLE_SCHEMA='mydbname'
ANDREFERENCED_TABLE_NAMEISNOTNULL;
自增
导出创建自增字段的语句
SELECT
CONCAT('ALTERTABLE`',TABLE_NAME,'`','MODIFYCOLUMN`',COLUMN_NAME,'`',UPPER(COLUMN_TYPE),'NOTNULLAUTO_INCREMENTCOMMENT"',COLUMN_COMMENT,'";')as'ADD_AUTO_INCREMENT'
FROM
information_schema.COLUMNS
WHERE
TABLE_SCHEMA='mydbname'
ANDEXTRA=UPPER('AUTO_INCREMENT')
ORDERBY
TABLE_NAMEASC;
创建删除所有自增字段
SELECT
CONCAT('ALTERTABLE`',TABLE_NAME,'`','MODIFYCOLUMN`',COLUMN_NAME,'`',UPPER(COLUMN_TYPE),'NOTNULL;')as'DELETE_AUTO_INCREMENT'
FROM
information_schema.COLUMNS
WHERE
TABLE_SCHEMA='mydbname'
ANDEXTRA=UPPER('AUTO_INCREMENT')
ORDERBY
TABLE_NAMEASC;
索引
导出所有索引
SELECT
CONCAT(
'ALTERTABLE`',
TABLE_NAME,
'`',
'ADD',
IF
(
NON_UNIQUE=1,
CASE
UPPER(INDEX_TYPE)
WHEN'FULLTEXT'THEN
'FULLTEXTINDEX'
WHEN'SPATIAL'THEN
'SPATIALINDEX'ELSECONCAT('INDEX`',INDEX_NAME,'`USING',INDEX_TYPE)
END,
IF
(
UPPER(INDEX_NAME)='PRIMARY',
CONCAT('PRIMARYKEYUSING',INDEX_TYPE),
CONCAT('UNIQUEINDEX`',INDEX_NAME,'`USING',INDEX_TYPE))),
CONCAT('(`',COLUMN_NAME,'`)'),
';'
)AS'ADD_ALL_INDEX'
FROM
information_schema.STATISTICS
WHERE
TABLE_SCHEMA='mydbname'
ORDERBY
TABLE_NAMEASC,
INDEX_NAMEASC;
删除所有索引
SELECT
CONCAT('ALTERTABLE`',TABLE_NAME,'`',CONCAT('DROP',IF(UPPER(INDEX_NAME)='PRIMARY','PRIMARYKEY',CONCAT('INDEX`',INDEX_NAME,'`'))),';')AS'DELETE_ALL_INDEX'
FROM
information_schema.STATISTICS
WHERE
TABLE_SCHEMA='mydbname'
ORDERBY
TABLE_NAMEASC;
数据合并
在数据迁移合并的时候，比较棘手的是不同数据库主键重复，那么我们就要批量修改主键的值，为了避免重复我们可以把自增的数字改为字符串
步骤基本上有以下几步
- 取消主键自增
- 删除所有外键
- 修改主键字段为varchar
- 添加所有外键
- 修改主键的值
- 合并数据
修改主键值的时候要注意
如果包含id和pid这种自关联的情况下是不能直接修改值的，就需要先删除约束再添加。
比如
删除自约束
ALTERTABLE`t_director`DROPFOREIGNKEY`fk_directorpid`;
修改值
updatet_directorsetdirectorid=directorid+100000000; updatet_directorsetdirectorid=CONV(directorid,10,36); updatet_directorsetdirectorpid=directorpid+100000000WHEREdirectorpidisnotnull; updatet_directorsetdirectorpid=CONV(directorpid,10,36)WHEREdirectorpidisnotnull;
添加自约束
ALTERTABLEt_directorADDCONSTRAINTfk_directorpidFOREIGNKEY(directorpid)REFERENCESt_director(directorid)ONDELETECASCADEONUPDATECASCADE;
注意
CONV(directorpid,10,36)后两个参数为原数字进制和要转换后的进制。
第一个参数只要内容是数字就算类型为varchar也可以转换。
以上就是Mysql中索引和约束的示例语句的详细内容，更多关于MySQL索引和约束的资料请关注毛票票其它相关文章！