mysql - SuperProjectGit/super-repository GitHub Wiki

mysql处理重复数据时 insert replace ON DUPLICATE KEY UPDATE

我们在使用数据库时可能会经常遇到这种情况。如果一个表在一个字段上建立了唯一索引，当我们再向这个表中使用已经存在的键值插入一条记录，那将会抛出一个主键冲突的错误，这个时候我们该怎么处理？

INSERT

INSERT IGNORE INTO books (name) VALUES ('MySQL Manual') 如果碰到主键或者唯一索引冲突的情况，则忽略执行，影响的行数为0

INSERT INTO clients (client_id,client_name,client_type) SELECT 10345,'IBM','advertising' FROM dual WHERE not exists (select * from clients where clients.client_id=10345);

ON DUPLICATE KEY UPDATE

如果您指定了ON DUPLICATE KEY UPDATE，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则执行旧行UPDATE。 INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1; 这种情况不会生成新的索引

如果行作为新记录被插入，则受影响行的值为1；如果原有的记录被更新，则受影响行的值为2。当使用ON DUPLICATE KEY UPDATE时，DELAYED选项被忽略。

REPLACE语句

使用REPLACE插入一条记录时，如果不重 复，REPLACE就和INSERT的功能一样，如果有重复记录，REPLACE就使用新记录的值来替换原来的记录值。 使用REPLACE的最大好处就是可以将DELETE和INSERT合二为一，形成一个原子操作。这样就可以不必考虑在同时使用DELETE和INSERT时添加事务等复杂操作了。

在使用REPLACE时，表中必须有唯一索引，而且这个索引所在的字段不能允许空值，否则REPLACE就和INSERT完全一样的。

在执行REPLACE后，系统返回了所影响的行数，如果返回1，说明在表中并没有重复的记录，如果返回2，说明有一条重复记录，系统自动先调用了 DELETE删除这条记录，然后再记录用INSERT来插入这条记录。如果返回的值大于2，那说明有多个唯一索引，有多条记录被删除和插入。这时候需要生成新的索引，REPLACE的语法和INSERT非常的相似，如下面的REPLACE语句是插入或更新一条记录。

REPLACE INTO users (id,name,age) VALUES(123, ‘赵本山’, 50); 　 插入多条记录：

　　REPLACE INTO users(id, name, age) 　　VALUES(123, ‘赵本山’, 50), (134,’Mary’,15);

上面曾提到REPLACE可能影响3条以上的记录，这是因为在表中有超过一个的唯一索引。在这种情况下，REPLACE将考虑每一个唯一索引，并对每一 个索引对应的重复记录都删除，然后插入这条新记录。假设有一个table1表，有3个字段a, b, c。它们都有一个唯一索引。

CREATE TABLE table1(a INT NOT NULL UNIQUE,b INT NOT NULL UNIQUE,c INT NOT NULL UNIQUE);

假设table1中已经有了3条记录

　　a b c

　　1 1 1

　　2 2 2

　　3 3 3

下面我们使用REPLACE语句向table1中插入一条记录。

　　REPLACE INTO table1(a, b, c) VALUES(1,2,3);

返回的结果如下

　　Query OK, 4 rows affected (0.00 sec)

在table1中的记录如下

　　a b c

　　1 2 3

则可以看到，REPLACE将原先的3条记录都删除了，然后将（1, 2, 3）插入。

数据库事务

事务具有四个特征：原子性（ Atomicity ）、一致性（ Consistency ）、隔离性（ Isolation ）和持续性（ Durability ）。这四个特性简称为 ACID 特性。

  1. 原子性。事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做

  2. 一致性。事 务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统 运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是 不一致的状态。

  3. 隔离性。一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。

  4. 持续性。也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

数据库隔离级别

READ_UNCOMMITED

  • 事务对当前被读取的数据不加锁；
  • 事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加 行级共享锁，直到事务结束才释放。 表现：
  • 事务1读取某行记录时，事务2也能对这行记录进行读取、更新；当事务2对该记录进行更新时，事务1再次读取该记录，能读到事务2对该记录的修改版本，即使该修改尚未被提交。
  • 事务1更新某行记录时，事务2不能对这行记录做更新，直到事务1结束。

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

READ_COMMITED

  • 事务对当前被读取的数据加 行级共享锁（当读到时才加锁），一旦读完该行，立即释放该行级共享锁；
  • 事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加 行级排他锁，直到事务结束才释放。 表现：
  • 事务1读取某行记录时，事务2也能对这行记录进行读取、更新；当事务2对该记录进行更新时，事务1再次读取该记录，读到的只能是事务2对其更新前的版本，要不就是事务2提交后的版本。
  • 事务1更新某行记录时，事务2不能对这行记录做更新，直到事务1结束。

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

REPEATABLE READ

  • 事务在读取某数据的瞬间（就是开始读取的瞬间），必须先对其加 行级共享锁，直到事务结束才释放；
  • 事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加 行级排他锁，直到事务结束才释放。 表现：
  • 事务1读取某行记录时，事务2也能对这行记录进行读取、更新；当事务2对该记录进行更新时，事务1再次读取该记录，读到的仍然是第一次读取的那个版本。
  • 事务1更新某行记录时，事务2不能对这行记录做更新，直到事务1结束。

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

SERIALIZABLE

  • 事务在读取数据时，必须先对其加 表级共享锁 ，直到事务结束才释放；
  • 事务在更新数据时，必须先对其加 表级排他锁 ，直到事务结束才释放。 表现：
  • 事务1正在读取A表中的记录时，则事务2也能读取A表，但不能对A表做更新、新增、删除，直到事务1结束。
  • 事务1正在更新A表中的记录时，则事务2不能读取A表的任意记录，更不可能对A表做更新、新增、删除，直到事务1结束

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

一个SQL查询出每门课程的成绩都大于80的学生姓名

方法一

思路：采用逆向思维想想。。。。。。求三门成绩都大于80的人，也可以是使先查出有成绩小于80 的人，再除去这些人不就是三门成绩都大于80的人了么？

具体办法：先扫描表，查出有成绩小于80的人的姓名，然后再次扫描表，用not in 或not exists 方法。

//not in SELECT DISTINCT A.name FROM Student A WHERE A.name not in( SELECT Distinct S.name FROM Student S WHERE S.score <80)

//not exists SELECT DISTINCT A.name From Student A where not exists (SELECT 1 From Student S Where S.score <80 AND S.name =A.name) /*exists 详解

取出 外表第一条数据 ，然后与内表 根据连接条件 ， 形成一条或多条数据，判断这些生成的数据中是否存在 或者是不存在符合where条件的 。结果为ture的那条外表 记录旧被查询出来！

实例过程： 取出外表的第一条记录， 和内表通过姓名条件连接，这时候产生2两记录， 根据 not exists是判断不存在。 条件是 score<80 . 而这两条记录存在一条记录小于80，所以于not exists 不符合， 该条记录不被查出。

*/

方法二

SELECT S.name FROM Student S GROUP BY S.name Having MIN(S.score)>=80

mysql 字段类型不一致导致索引失效

mysql进行表关联查询时，关联字段两个表字段类型不一致时，则无法使用索引

REF

mysql索引

insert加锁机制

BTree

select锁

MySQL 慢查询的因素

mysql 加锁过程

mysql redolog

innodb

数据的读取和存储方式