search
HomeDatabaseMysql TutorialMySQL批量插入遇上唯一索引避免方法

以前使用SQL Server进行表分区的时候就碰到很多关于唯一索引的问题,今天我们来了解MySQL唯一索引的一些知识:包括如何创建,如何批量插入,还有一些技巧上SQL,感兴趣的朋友可以了解下

一、背景

以前使用SQL Server进行表分区的时候就碰到很多关于唯一索引的问题:Step8:SQL Server 当表分区遇上唯一约束,没想到在MySQL的分区中一样会遇到这样的问题:MySQL表分区实战。

今天我们来了解MySQL唯一索引的一些知识:包括如何创建,如何批量插入,还有一些技巧上SQL;

这些问题的根源在什么地方?有什么共同点?MySQL中也有分区对齐的概念?唯一索引是在很多系统中都会出现的要求,有什么办法可以避免?它对性能的影响有多大?

二、过程

(一) 导入差异数据,忽略重复数据,IGNORE INTO的使用

在MySQL创建表的时候,我们通常创建一个表的时候是以一个自增ID值作为主键,那么MySQL就会以PRIMARY KEY作为聚集索引键和主键,既然是主键,那当然是唯一的了,所以重复执行下面的插入语句会报1062错误:如Figure1所示;
代码如下:
-- 创建测试表
CREATE TABLE `testtable` (
`Id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`UserId` INT(11) DEFAULT NULL,
`UserName` VARCHAR(10) DEFAULT NULL,
`UserType` INT(11) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 插入测试数据
INSERT INTO testtable(Id,UserId,UserName,UserType)
VALUES(1,101,'aa',1),(2,102,'bbb',2),(3,103,'ccc',3);

但是在实际的生产环境中,需求往往是需要在UserId键值中设置唯一索引,今天我就以这个作为示例,进行唯一索引的测试:
代码如下:
-- 创建测试表1
CREATE TABLE `testtable1` (
`Id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`UserId` INT(11) DEFAULT NULL,
`UserName` VARCHAR(10) DEFAULT NULL,
`UserType` INT(11) DEFAULT NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `IX_UserId` (`UserId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 创建测试表2
CREATE TABLE `testtable2` (
`Id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`UserId` INT(11) DEFAULT NULL,
`UserName` VARCHAR(10) DEFAULT NULL,
`UserType` INT(11) DEFAULT NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `IX_UserId` (`UserId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 插入测试数据1
INSERT INTO testtable1(Id,UserId,UserName,UserType)
VALUES(1,101,'aa',1),(2,102,'bbb',2),(3,103,'ccc',3);

-- 插入测试数据2
INSERT INTO testtable2(Id,UserId,UserName,UserType)
VALUES(1,201,'aaa',1),(2,202,'bbb',2),(3,203,'ccc',3),(4,101,'xxxx',5);

(Figure2:记录)

(Figure3:testtable2记录)

通过执行上面的SQL脚本,我们在testtable1和testtable2都创建了唯一索引:UNIQUE KEY `IX_UserId` (`UserId`),这就说明UserId在testtable1和testtable2表中都是唯一的,如果把testtable2的数据批量导入到testtable1,如果执行下面【导入1】的SQL,就会出现1062的错误,导致整个过程会回滚,没有达到导入差异数据的目的。
代码如下:
INSERT INTO testtable1(UserId,UserName,UserType)
SELECT UserId,UserName,UserType FROM testtable2;

(Figure4:Duplicate entry '101' for key 'IX_UserId')

MySQL提供一个关键字:IGNORE,这个关键字判断每条记录是否存在,是否违反饿了表中的唯一索引,如果存在就不插入,而不存在的记录就会插入。
代码如下:
-- 导入2
INSERT IGNORE INTO testtable1(UserId,UserName,UserType)
SELECT UserId,UserName,UserType FROM testtable2;

所以执行完【导入2】,就会产生Figure5的结果,这已经达到了我们的目的了,但是你有没发现自增的ID值跳过了一些值,这是因为我们之前执行【导入1】失败造成的,虽然我们的事务回滚了,但是自增ID会出现断层。在SQL Server中也会有这样的问题。扩展阅读:简单实用SQL脚本Part:查找SQL Server 自增ID值不连续记录

(5:IGNORE效果)

1. 把testtable1和testtable2分别回滚到Figure2和Figure3的状态(使用TRUNCATE TABLE命名再执行Insert语句),这个时候再执行下面的SQL,看有什么效果:
代码如下:
-- 导入3
REPLACE INTO testtable1(UserId,UserName)
SELECT UserId,UserName FROM testtable2;

(Figure6:REPLACE效果)

从上图Figure6中,我们可以看到:UserId为101的记录发生了改变,不单UserName修改了,而且UserType也变为NULL了。

所以,如果导入中发现了重复的,先删除再插入,如果记录有多个字段,在插入的时候如果有的字段没有赋值,那么新插入的记录这些字段为空(新插入记录的UserType都为NULL)。

需要注意的是,当你replace的时候,如果被插入的表如果没有指定列,会用NULL表示,而不是这个表原来的内容。如果插入的内容列和被插入的表列一样,则不会出现NULL。

2. 如果我们表结构UserType字段不允许为空,而且没有默认值的情况,执行【导入3】会发生什么事情呢?

(Figure7:返回警告信息)

(Figure8:UserType被设置为0)

通过Figure7和Figure8,我们知道数据记录还是插入了,只是返回Field 'UserType' doesn't have a default value的警告,插入记录的UserType字段都被设置为0('UserType' 为int数据类型)。

3. 如果我们希望导入的时候一起更新UserType字段的值,这自然很简单了,使用下面的SQL脚本就可以解决:
代码如下:
-- 导入4
REPLACE INTO testtable1(UserId,UserName,UserType)
SELECT UserId,UserName,UserType FROM testtable2;

把testtable1和testtable2分别回滚到Figure2和Figure3的状态(使用TRUNCATE TABLE命名再执行Insert语句),这个时候再执行下面的SQL,看有什么效果:
代码如下:
-- 导入5
INSERT INTO testtable1(UserId,UserName)
SELECT UserId,UserName FROM testtable2
ON DUPLICATE KEY UPDATE
testtable1.UserName = testtable2.UserName;

UserId为101的记录:更新了UserName的值,保留了UserType的值;但是由于【导入5】中没有指定UserType,所以新插入记录的UserType是为NULL的。
代码如下:
-- 导入6
INSERT INTO testtable1(UserId,UserName,UserType)
SELECT UserId,UserName,UserType FROM testtable2
ON DUPLICATE KEY UPDATE
testtable1.UserName = testtable2.UserName;

(Figure11:保留UserType值)

对比Figure2、Figure3与Figure11,只插入testtable2表的UserId,UserName字段,但是保留testtable1表的UserType字段。如果发现有重复的记录,做更新操作;在原有记录基础上,更新指定字段内容,其它字段内容保留。

(四) 总结

当在一个UNIQUE键上插入包含重复值的记录时,默认的insert会报1062错误,MYSQL可以通过以上三种不同的方式和你的业务逻辑进行处理。

三、参考文献

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Explain the InnoDB Buffer Pool and its importance for performance.Explain the InnoDB Buffer Pool and its importance for performance.Apr 19, 2025 am 12:24 AM

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

MySQL vs. Other Programming Languages: A ComparisonMySQL vs. Other Programming Languages: A ComparisonApr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages ​​such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages ​​have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Learning MySQL: A Step-by-Step Guide for New UsersLearning MySQL: A Step-by-Step Guide for New UsersApr 19, 2025 am 12:19 AM

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL: Essential Skills for Beginners to MasterMySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AM

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL: Structured Data and Relational DatabasesMySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL: Key Features and Capabilities ExplainedMySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AM

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

The Purpose of SQL: Interacting with MySQL DatabasesThe Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AM

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

MySQL for Beginners: Getting Started with Database ManagementMySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

Dreamweaver Mac version

Dreamweaver Mac version

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools