【Mysql】Navicat数据导出和Mysql唯一约束的坑

本文讲述了在处理MySQL数据时遇到的一个问题,即使用Navicat导出CSV并尝试重新导入时,由于唯一约束和空值处理的差异导致导入失败。在故障排查后发现,MySQL的唯一约束在包含空值时可能会失效,而Navicat在导出CSV时将空值写为"",导入时变成空字符串,违反了唯一约束。解决方案是先将CSV中的空字符串设为NULL,再导入到目标表。

一个简单的需求引发的问题

简单的需求

收到一个Mysql数据清理的需求,需要按照查询条件清理部分数据,按照操作规范:

  • 用Navicat(导出UTF8 CSV文件的方式)备份即将被清理的数据,记录数据量
  • 清理数据,核对数据量,没有问题

变更

后面需求变化,这部分数据不需要删除了,需要rollback,着手恢复数据

  • 用Navicat导入数据,报错:违反唯一约束
  • 核实库表变动,反馈表结构和数据都没做过后续变更
  • 利用 create table like 创建中间表导入数据,依然报相同错误

故障排查

  • 删除中间表唯一约束,数据可以正常导入,按照这个逻辑,当时导出数据的时候数据就违反唯一约束了,首先怀疑是否是Mysql唯一约束存在bug
  • 测试重复数据之后加唯一约束或者唯一约束之后插入重复数据都报错,说明问题可能出在数据本身
  • 核查重复数据,发现一个问题,现在已加唯一约束的表中也存在违反唯一约束的数据,但数据库没有报错

问题确定

  • 测试Mysql的唯一约束,发现其和sqlserver、ORACLE相比存在一个差异。当Mysql唯一约束的几个字段中存在空值的时候,唯一约束会失效。比如唯一约束为 ID+VALUES,对于values(1,NULL)这样的一条数据,在sqlserver中重复插入会报违反唯一约束,但Mysql不会报错(认为不违反唯一约束)。
  • Navicat查询结果导出为CSV后导入存在数据差异,因为选择了字符串分隔符,所以空值在CSV里面写入为”“,当导入时这个数据就成了一个空字符串,导致数据差异。

解决方式

  1. 将CSV导入中间表,然后将空字符串设为NULL
  2. 将数据从中间表导入目标表

后续

切换导出查询结果工具,注意这个差异。

test sql

---------------- mysql
<think>好的,用户现在的问题是,如何在MySQL Navicat导出已有的外键约束的添加语法。我需要仔细考虑这个问题的解决步骤,并给出详细的解释代码示例。首先,用户可能已经使用Navicat这个数据库管理工具,想要导出外键约束的DDL语句。Navicat提供了多种方式来查看导出表结构,包括外键约束。常见的做法是通过“设计表”功能查看外键,但直接导出创建外键的SQL语句可能需要其他方法。 接下来,我需要考虑如何通过Navicat的界面操作来获取外键的SQL语句。比如,用户可能需要在表的DDL信息中找到外键定义,或者在导出SQL文件时包含外键信息。此外,使用SHOW CREATETABLE命令也是一个有效的方法,可以直接在查询工具中执行,获取完整的建表语句,其中包含外键约束。 然后,我需要编写具体的步骤,指导用户如何操作Navicat,例如通过右键点击表选择“对象信息”或使用查询窗口执行SHOW CREATE TABLE。同时,提供示例代码,展示如何从建表语句中提取外键约束的SQL。还要考虑用户可能的疑问,比如如何批量导出所有表的外键,这时候可能需要编写SQL脚本遍历information_schema中的外键信息。此外,用户可能关心不同版本的Navicat是否有不同的操作方式,或者是否有其他替代方法。 最后,要确保回答清晰,步骤详细,并附上代码示例,帮助用户实际应用。同时,相关问题的提出要围绕外键约束的其他操作,比如修改、删除,或者如何确保外键约束的完整性,以及不同数据库版本的差异等。</think>Navicat导出外键约束的创建语句可以通过以下步骤实现: 1. **通过DDL查看外键语句**: ```sql -- 右键点击目标表 -> 对象信息 -> DDL -- 在显示的建表语句中找到CONSTRAINT部分 CREATE TABLE orders ( order_id INT PRIMARY KEY, user_id INT, CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ); ``` 2. **使用SQL查询获取外键信息**: ```sql -- 新建查询窗口执行以下语句 SHOW CREATE TABLE your_table_name; -- 示例输出: CREATE TABLE `orders` ( `order_id` int(11) NOT NULL, `user_id` int(11) DEFAULT NULL, PRIMARY KEY (`order_id`), KEY `fk_user` (`user_id`), CONSTRAINT `fk_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ``` 3. **批量导出所有外键(通过SQL查询)**: ```sql SELECT CONCAT( 'ALTER TABLE ', TABLE_NAME, ' ADD CONSTRAINT ', CONSTRAINT_NAME, ' FOREIGN KEY (', COLUMN_NAME, ') REFERENCES ', REFERENCED_TABLE_NAME, '(', REFERENCED_COLUMN_NAME, ') ON DELETE ', DELETE_RULE, ' ON UPDATE ', UPDATE_RULE, ';' ) AS foreign_key_sql FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'your_database_name' AND REFERENCED_TABLE_NAME IS NOT NULL; ``` **操作解释**: 1. 通过DDL查看是最直接的方式,可以立即看到完整的外键定义 2. SHOW CREATE TABLE 会返回完整的建表语句,其中包含所有约束定义 3. 查询information_schema可以批量生成所有外键的创建语句,适合需要迁移或备份约束的场景
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值