mysql中索引和约束的语句分享

爱被打了一巴掌 2024-03-23 18:06 40阅读 0赞

![d9fab2a41d91247df1a8c2e2553afa07.jpeg][]

本文主要介绍了Mysql中索引和约束的相关资料，帮助大家更好的理解和使用MySQL，感兴趣的朋友可以了解下

### 外键 ###

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

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 </td> 
 <td> <code>SELECT</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>TABLE_NAME,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>COLUMN_NAME,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>CONSTRAINT_NAME,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>REFERENCED_TABLE_NAME,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>REFERENCED_COLUMN_NAME</code> <code>FROM</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>INFORMATION_SCHEMA.KEY_COLUMN_USAGE</code> <code>WHERE</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>TABLE_SCHEMA = </code><code>'mydbname'</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>AND</code> <code>REFERENCED_TABLE_NAME = </code><code>'表名'</code><code>;</code> </td> 
 </tr> 
 </tbody> 
</table>

#### 导出所有外键语句 ####

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 </td> 
 <td> <code>SELECT</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>CONCAT(</code><code>'ALTER TABLE '</code><code>, TABLE_NAME, </code><code>' ADD CONSTRAINT '</code><code>, CONSTRAINT_NAME, </code><code>' FOREIGN KEY ('</code><code>, COLUMN_NAME, </code><code>') REFERENCES '</code><code>, REFERENCED_TABLE_NAME, </code><code>'('</code><code>, REFERENCED_COLUMN_NAME, </code><code>') ON DELETE CASCADE ON UPDATE CASCADE;'</code><code>)</code> <code>FROM</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>INFORMATION_SCHEMA.KEY_COLUMN_USAGE</code> <code>WHERE</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>TABLE_SCHEMA = </code><code>'mydbname'</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>AND</code> <code>REFERENCED_TABLE_NAME </code><code>IS</code> <code>NOT</code> <code>NULL</code><code>;</code> </td> 
 </tr> 
 </tbody> 
</table>

#### 删除所有外键语句 ####

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 </td> 
 <td> <code>SELECT</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>CONCAT(</code><code>'ALTER TABLE '</code><code>, TABLE_NAME, </code><code>' DROP FOREIGN KEY '</code><code>, CONSTRAINT_NAME, </code><code>';'</code><code>)</code> <code>FROM</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>INFORMATION_SCHEMA.KEY_COLUMN_USAGE</code> <code>WHERE</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>TABLE_SCHEMA = </code><code>'mydbname'</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>AND</code> <code>REFERENCED_TABLE_NAME </code><code>IS</code> <code>NOT</code> <code>NULL</code><code>;</code> </td> 
 </tr> 
 </tbody> 
</table>

### 自增 ###

#### 导出创建自增字段的语句 ####

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 </td> 
 <td> <code>SELECT</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>CONCAT( </code><code>'ALTER TABLE `'</code><code>, TABLE_NAME, </code><code>'` '</code><code>, </code><code>'MODIFY COLUMN `'</code><code>, COLUMN_NAME, </code><code>'` '</code><code>, </code><code>UPPER</code><code>( COLUMN_TYPE ), </code><code>' NOT NULL AUTO_INCREMENT COMMENT "'</code><code>,COLUMN_COMMENT,</code><code>'";'</code> <code>) </code><code>as</code> <code>'ADD_AUTO_INCREMENT'</code> <code>FROM</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>information_schema.COLUMNS </code> <code>WHERE</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>TABLE_SCHEMA = </code><code>'mydbname'</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>AND</code> <code>EXTRA = </code><code>UPPER</code><code>( </code><code>'AUTO_INCREMENT'</code> <code>) </code> <code>ORDER</code> <code>BY</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>TABLE_NAME </code><code>ASC</code><code>;</code> </td> 
 </tr> 
 </tbody> 
</table>

#### 创建删除所有自增字段 ####

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 </td> 
 <td> <code>SELECT</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>CONCAT( </code><code>'ALTER TABLE `'</code><code>, TABLE_NAME, </code><code>'` '</code><code>, </code><code>'MODIFY COLUMN `'</code><code>, COLUMN_NAME, </code><code>'` '</code><code>, </code><code>UPPER</code><code>( COLUMN_TYPE ), </code><code>' NOT NULL;'</code> <code>) </code><code>as</code> <code>'DELETE_AUTO_INCREMENT'</code> <code>FROM</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>information_schema.COLUMNS </code> <code>WHERE</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>TABLE_SCHEMA = </code><code>'mydbname'</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>AND</code> <code>EXTRA = </code><code>UPPER</code><code>( </code><code>'AUTO_INCREMENT'</code> <code>) </code> <code>ORDER</code> <code>BY</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>TABLE_NAME </code><code>ASC</code><code>;</code> </td> 
 </tr> 
 </tbody> 
</table>

### 索引 ###

#### 导出所有索引 ####

<table> 
 <tbody> 
 <tr> 
 <td> 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 </td> 
 <td> <code>SELECT</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>CONCAT(</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>'ALTER TABLE `'</code><code>,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>TABLE_NAME,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>'` '</code><code>,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>'ADD '</code><code>,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>IF</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>(</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>NON_UNIQUE = 1,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>CASE</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>UPPER</code><code>( INDEX_TYPE ) </code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>WHEN</code> <code>'FULLTEXT'</code> <code>THEN</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>'FULLTEXT INDEX'</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>WHEN</code> <code>'SPATIAL'</code> <code>THEN</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>'SPATIAL INDEX'</code> <code>ELSE</code> <code>CONCAT( </code><code>'INDEX `'</code><code>, INDEX_NAME, </code><code>'` USING '</code><code>, INDEX_TYPE ) </code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>END</code><code>,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>IF</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>(</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>UPPER</code><code>( INDEX_NAME ) = </code><code>'PRIMARY'</code><code>,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>CONCAT( </code><code>'PRIMARY KEY USING '</code><code>, INDEX_TYPE ),</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>CONCAT( </code><code>'UNIQUE INDEX `'</code><code>, INDEX_NAME, </code><code>'` USING '</code><code>, INDEX_TYPE ))),</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>CONCAT( </code><code>'(`'</code><code>, COLUMN_NAME, </code><code>'`)'</code> <code>),</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>';'</code> <code>) </code><code>AS</code> <code>'ADD_ALL_INDEX'</code> <code>FROM</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>information_schema.</code><code>STATISTICS</code> <code>WHERE</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>TABLE_SCHEMA = </code><code>'mydbname'</code> <code>ORDER</code> <code>BY</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>TABLE_NAME </code><code>ASC</code><code>,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>INDEX_NAME </code><code>ASC</code><code>;</code> </td> 
 </tr> 
 </tbody> 
</table>

#### 删除所有索引 ####

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 </td> 
 <td> <code>SELECT</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>CONCAT( </code><code>'ALTER TABLE `'</code><code>, TABLE_NAME, </code><code>'` '</code><code>, CONCAT( </code><code>'DROP '</code><code>, IF ( </code><code>UPPER</code><code>( INDEX_NAME ) = </code><code>'PRIMARY'</code><code>, </code><code>'PRIMARY KEY'</code><code>, CONCAT( </code><code>'INDEX `'</code><code>, INDEX_NAME, </code><code>'`'</code> <code>))), </code><code>';'</code> <code>) </code><code>AS</code> <code>'DELETE_ALL_INDEX'</code> <code>FROM</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>information_schema.</code><code>STATISTICS</code> <code>WHERE</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>TABLE_SCHEMA = </code><code>'mydbname'</code> <code>ORDER</code> <code>BY</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>TABLE_NAME </code><code>ASC</code><code>;</code> </td> 
 </tr> 
 </tbody> 
</table>

### 数据合并 ###

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

步骤基本上有以下几步

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

修改主键值的时候要注意

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

比如

删除自约束

<table> 
 <tbody> 
 <tr> 
 <td> 1 </td> 
 <td> <code>ALTER</code> <code>TABLE</code> <code>`t_director` </code><code>DROP</code> <code>FOREIGN</code> <code>KEY</code> <code>`fk_directorpid`;</code> </td> 
 </tr> 
 </tbody> 
</table>

修改值

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 </td> 
 <td> <code>update</code> <code>t_director </code><code>set</code> <code>directorid=directorid+100000000;</code> <code>update</code> <code>t_director </code><code>set</code> <code>directorid=CONV(directorid,10,36);</code> <code>update</code> <code>t_director </code><code>set</code> <code>directorpid=directorpid+100000000 </code><code>WHERE</code> <code>directorpid </code><code>is</code> <code>not</code> <code>null</code><code>;</code> <code>update</code> <code>t_director </code><code>set</code> <code>directorpid=CONV(directorpid,10,36) </code><code>WHERE</code> <code>directorpid </code><code>is</code> <code>not</code> <code>null</code><code>;</code> </td> 
 </tr> 
 </tbody> 
</table>

添加自约束

<table> 
 <tbody> 
 <tr> 
 <td> 1 </td> 
 <td> <code>ALTER</code> <code>TABLE</code> <code>t_director </code><code>ADD</code> <code>CONSTRAINT</code> <code>fk_directorpid </code><code>FOREIGN</code> <code>KEY</code> <code>(directorpid) </code><code>REFERENCES</code> <code>t_director(directorid) </code><code>ON</code> <code>DELETE</code> <code>CASCADE</code> <code>ON</code> <code>UPDATE</code> <code>CASCADE</code><code>;</code> </td> 
 </tr> 
 </tbody> 
</table>

注意

> CONV(directorpid,10,36)后两个参数为原数字进制和要转换后的进制。
> 
> 第一个参数只要内容是数字就算类型为varchar也可以转换。

以上就是Mysql中索引和约束的示例语句的详细内容，希望对你有所帮助。

转自：[微点阅读][Link 1] [https://www.weidianyuedu.com][Link 1]

[d9fab2a41d91247df1a8c2e2553afa07.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/a594143167764d158f9301b3ff3c006d.png
[Link 1]: https://www.weidianyuedu.com