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

最新推荐文章于 2023-03-18 21:59:40 发布
原创 最新推荐文章于 2023-03-18 21:59:40 发布 · 1.3k 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#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
最低0.47元/天 解锁文章

新学期VIP享超值加赠
Navicat和SQLynx功能比较三（数据导出：使用MySQL近千万数据测试）
lgbingyu00的博客
06-17 3958
数据导出的功能在数据库管理工具中是最普遍的功能之一。Navicat在大数据量的导出中表现比较稳定，同时Navicat性能也比较优异。但Navicat的性能上在千万级别导出相对SQLynx相差有3倍以上，同时Navicat主要是表的导出，而SQLynx同时支持表的导出和复杂查询结果导出，在开发场景和企业级需求中都能很好地满足企业需求。用户可以根据实际情况选择Navicat或SQLynx来使用。
mysql show create table_mysql – SHOW CREATE TABLE输出中缺少外键约束
weixin_34982726的博客
02-17 1499
我发现SHOW CREATE TABLE没有像我期望的那样显示外键约束.为了演示,这是MySQL manual的一个例子：CREATE TABLE parent (id INT NOT NULL,PRIMARY KEY (id)) ENGINE=INNODB;CREATE TABLE child (id INT, parent_id INT,INDEX par_ind (parent_id),FO...
Navicatfor mysql怎么设置唯一约束
weixin_34405557的博客
07-28 4159
在设计表时，上面会有索引这一项 点开，在栏位处选择你需要设置的字段，然后选择unique索引类型即可 转载于:https://www.cnblogs.com/xyblogs/p/9382020.html
navicat mysql唯一约束_navicat表约束怎么弄
weixin_35738080的博客
01-19 8405
数据库中约束分为一下几种●主键约束(Primary Key constraint)：要求主键列数据唯一，并且不允许为空。●唯一约束(Unique constraint)：要求该列唯一，允许为空，但只能出现一个空值。●检查约束(Check constraint)：某列取值范围限制，格式限制等，如有关年龄、邮箱(必须有@)的约束。●默认约束(Default constraint)：某列的默认值，如在数...
解决Navicat无法导入CSV文件
weixin_55944301的博客
03-18 4565
SQL数据库管理工具Navicat导表错误如何解决
ORA-00001: 违反唯一约束条件 (IDS.SYS_C0016812)
qq_36198235的博客
04-15 1205
看后台窗口报错信息是某个表批量插入时报错。于是查找该表是否有自增序列。 select SEQUENCE_OWNER,SEQUENCE_NAME from dba_sequences where sequence_owner=’***’; select SEQ_ID.nextval from dual; select a.,b. from user_constraints a inner join user_cons_columns b on a.table_name=b.table_name where a
MySQL导出所有Index和约束的方法
u010316405的专栏
11-26 548
本文汇总了MySQL导出所有Index 和 约束的方法，提供给大家以方便大家查询使用。具体如下： 1. 导出创建自增字段语句： ? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
Navicat for MySQL导出表结构脚本的简单方法
12-16
使用MySQL可视化工具Navicat导出MySQL的表结构脚本的方法。 1、右键Navicat中的数据库→数据传输（Data Transfer）。 2、左边数据库对象（Database Objects）中选择对应的表，右边目标（Target）中选择文件（File）...
用NavicatforMySQL进行数据库的导出.pdf
09-30
* 安全可靠：Navicat for MySQL的导出功能支持加密和压缩，确保数据的安全和完整性。 Navicat for MySQL是一个功能强大且易于使用的数据库管理工具，非常适合数据库管理员和开发者使用。其导出功能可以满足不同的...
mysql为什么就取出来一条数据库_为什么数据库中有多条数据，单只能取出一条...
weixin_42312523的博客
01-19 968
正在做一个web练手项目，使用mysql数据库，和tomcat的连接池。在连接数据库读取数据时，数据库中有多条记录，但却只能取出一条，而直接使用navicat for mysql直接运行查询语句却能读取出所有数据，求指教啊。 代码如下：public List findAll(){List result = new ArrayList();Connection conn = null;Prepare...
MySQL表的约束
weixin_53027918的博客
03-03 1557
真正约束字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性。比如说我们的居民身份证，电话号码，都被要求是唯一的，这就需要约束。
ORA-00001: 违反唯一约束条件 --解决方法
热门推荐
beinlife的博客
07-27 19万+
1、错误 Caused by: java.sql.BatchUpdateException:ORA-00001: 违反唯一约束条件 (SOLEX.SYS_C0012537) 2、分析 ①(SOLEX.SYS_C0012537)是我们在数据库设置的约束条件的name，一般是主键ID约束 ②同hbm.xml文件找到主键的自增序列名称 SEQ_FEEPAY_COST_
ORA-00001: 违反唯一约束条件 的解决办法
zhangyuliang6430的博客
05-20 19万+
最近在做项目是测试的时候出现了这种错误：ORA-00001: 违反唯一约束条件 其实说白了这种错误就是 数据库的ID值，小于他的next number 由于表的ID是唯一的，所以用 select max(id) from test 查的该ID大于Sequences里面的开始ID，所以出现了该情况 为此，我们只要更改Sequences里面的”开始于“的 ID 大于 max(ID) 即可。问题...
sql 违反唯一约束条件_SQL数据分析-基础章节
weixin_36242784的博客
01-25 2153
一、SQL 概要1.SQL /数据库/MY SQL 辨析 MY SQL:存放数据管理容器SQL:查询数据的标准语句2.SQL语句及其种类 (1) SQL语句包含关键词、表名、列名等组合成的语句。关键词是指那些含义或者使用方案已经实现定义好的词。（2）根据指令类型，SQL分为3大类：数据定义语句 Data Definition Language --DDL:指用来创建或者删除存储数据用的数据库...
违反唯一约束条件
zhouyong0的专栏
06-12 3818
对象类型（索引还是约束） select object_type from user_objects where object_name='' 查看某表的约束条件 select constraint_name, constraint_type,search_condition, r_constraint_name from user_constraints where table_na
违反唯一条件约束
变是永远不变的！
03-13 1050
今天遇到了一个问题，违反唯一条件约束，根据后台报错信息找到库.表，然后根据对应插入的数据（一般是根据对应表中主键插入）查找是否有这条数据，有的话说明这条数据已经存在，然后可以删除这条数据，在做一次数据插入就可以了 ...
mysql navicat如何导出 已有的外键约束添加语法
最新发布
05-01
<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、付费专栏及课程。

余额充值