您的位置:首页 > 数据库 > > 正文

mysql 索引怎么实现(Mysql中索引和约束的示例语句)

更多 时间:2022-01-18 01:14:20 类别:数据库 浏览量:566

mysql 索引怎么实现

Mysql中索引和约束的示例语句

外键

查询一个表的主键是哪些表的外键

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • SELECT
  •     TABLE_NAME,
  •     COLUMN_NAME,
  •     CONSTRAINT_NAME,
  •     REFERENCED_TABLE_NAME,
  •     REFERENCED_COLUMN_NAME
  • FROM
  •     INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  • WHERE
  •     TABLE_SCHEMA = 'mydbname'
  •     AND REFERENCED_TABLE_NAME = '表名';
  • 导出所有外键语句

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • SELECT
  •     CONCAT('ALTER TABLE ', TABLE_NAME, ' ADD CONSTRAINT ', CONSTRAINT_NAME, ' FOREIGN KEY (', COLUMN_NAME, ') REFERENCES ', REFERENCED_TABLE_NAME, '(', REFERENCED_COLUMN_NAME, ') ON DELETE CASCADE ON UPDATE CASCADE;')
  • FROM
  •     INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  • WHERE
  •     TABLE_SCHEMA = 'mydbname'
  •     AND REFERENCED_TABLE_NAME IS NOT NULL;
  • 删除所有外键语句

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • SELECT
  •     CONCAT('ALTER TABLE ', TABLE_NAME, ' DROP FOREIGN KEY ', CONSTRAINT_NAME, ';')
  • FROM
  •     INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  • WHERE
  •     TABLE_SCHEMA = 'mydbname'
  •     AND REFERENCED_TABLE_NAME IS NOT NULL;
  • 自增

    导出创建自增字段的语句

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • SELECT
  •     CONCAT( 'ALTER TABLE `', TABLE_NAME, '` ', 'MODIFY COLUMN `', COLUMN_NAME, '` ', UPPER( COLUMN_TYPE ), ' NOT NULL AUTO_INCREMENT COMMENT "',COLUMN_COMMENT,'";' ) as 'ADD_AUTO_INCREMENT'
  • FROM
  •     information_schema.COLUMNS
  • WHERE
  •     TABLE_SCHEMA = 'mydbname'
  •     AND EXTRA = UPPER( 'AUTO_INCREMENT' )
  • ORDER BY
  •     TABLE_NAME ASC;
  • 创建删除所有自增字段

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • SELECT
  •     CONCAT( 'ALTER TABLE `', TABLE_NAME, '` ', 'MODIFY COLUMN `', COLUMN_NAME, '` ', UPPER( COLUMN_TYPE ), ' NOT NULL;' ) as 'DELETE_AUTO_INCREMENT'
  • FROM
  •     information_schema.COLUMNS
  • WHERE
  •     TABLE_SCHEMA = 'mydbname'
  •     AND EXTRA = UPPER( 'AUTO_INCREMENT' )
  • ORDER BY
  •     TABLE_NAME ASC;
  • 索引

    导出所有索引

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 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 ))),
  •         CONCAT( '(`', COLUMN_NAME, '`)' ),
  •         ';'
  • ) AS 'ADD_ALL_INDEX'
  • FROM
  •     information_schema.STATISTICS
  • WHERE
  •     TABLE_SCHEMA = 'mydbname'
  • ORDER BY
  •     TABLE_NAME ASC,
  •     INDEX_NAME ASC;
  • 删除所有索引

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • SELECT
  •     CONCAT( 'ALTER TABLE `', TABLE_NAME, '` ', CONCAT( 'DROP ', IF ( UPPER( INDEX_NAME ) = 'PRIMARY', 'PRIMARY KEY', CONCAT( 'INDEX `', INDEX_NAME, '`' ))), ';' ) AS 'DELETE_ALL_INDEX'
  • FROM
  •     information_schema.STATISTICS
  • WHERE
  •     TABLE_SCHEMA = 'mydbname'
  • ORDER BY
  •     TABLE_NAME ASC;
  • 数据合并

    在数据迁移合并的时候,比较棘手的是不同数据库主键重复,那么我们就要批量修改主键的值,为了避免重复我们可以把自增的数字改为字符串

    步骤基本上有以下几步

    • 取消主键自增
    • 删除所有外键
    • 修改主键字段为varchar
    • 添加所有外键
    • 修改主键的值
    • 合并数据

    修改主键值的时候要注意

    如果包含id和pid这种自关联的情况下是不能直接修改值的,就需要先删除约束再添加。

    比如

    删除自约束

  • ?
  • 1
  • ALTER TABLE `t_director` DROP FOREIGN KEY `fk_directorpid`;
  • 修改值

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • update t_director set directorid=directorid+100000000;
  • update t_director set directorid=CONV(directorid,10,36);
  •  
  • update t_director set directorpid=directorpid+100000000 WHERE directorpid is not null;
  • update t_director set directorpid=CONV(directorpid,10,36) WHERE directorpid is not null;
  • 添加自约束

  • ?
  • 1
  • ALTER TABLE t_director ADD CONSTRAINT fk_directorpid FOREIGN KEY (directorpid) REFERENCES t_director(directorid) ON DELETE CASCADE ON UPDATE CASCADE;
  • 注意

    CONV(directorpid,10,36)后两个参数为原数字进制和要转换后的进制。

    第一个参数只要内容是数字就算类型为varchar也可以转换。

    以上就是Mysql中索引和约束的示例语句的详细内容,更多关于MySQL 索引和约束的资料请关注开心学习网其它相关文章!

    原文链接:https://www.psvmc.cn/article/2020-12-23-mysql-constraint.html

    标签:mysql 索引 约束
    您可能感兴趣