MySQL 强化知识点

MySQL 强化知识点

1. INSERT INTO… ON DUPLICATE KEY 用法

​ 当在 insert 语句末尾指定了 on duplicate key update 语句时，如果新插入的新数据中 a列 的值已经在数据库中存在，则会执行后面的 update 语句。

通过一条SQL来说明：

INSERT INTO TableName(a,b,c) 
VALUES(1,2,3)
ON DUPLICATE KEY
UPDATE c=c+1;

解释：

​ 假设上面语句中字段 a 的属性为唯一 unique，当在 insert 语句末尾指定了 on duplicate key update 语句时，如果新插入的新数据中 a列 的值已经在数据库中存在，则会执行后面的 update语句，这时相当于执行了下面语句：

UPDATE TableName SET c=c+1 WHERE a=1;

反之，则执行正常的 insert 语句。

**需注意：**如果行作为新纪录被插入，则受影响行的值为1；如果原有的记录被更新，则受影响行的值为2。

通过另一条SQL来看看同时插入多行记录的情况：

字段a 被定义为 UNIQUE，并且原数据库表 table 中已存在记录(2,2,9) 和 (3,2,1)，如果插入记录的 a 值与原有记录重复，则更新原有记录，否则插入新行：

INSERT INTO TABLE (a,b,c) VALUES 
(1,2,3),
(2,5,7),
(3,3,6),
(4,8,2)
ON DUPLICATE KEY UPDATE b=VALUES(b);

如上执行，发现(2,5,7) 中的 a 与原有记录 (2,2,9) 发生唯一值冲突，则执行 ON DUPLICATE KEY UPDATE，将原有记录 (2,2,9) 更新成(2,5,9)，将(3,2,1) 更新为(3,3,1)，插入新纪录(1,2,3) 和 (4,8,2)

2. replace into…values 用法

replace into 跟 insert into 功能类似，不同点在于：replace into 首先尝试插入数据到表中，

1. 如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据；
2. 否则，直接插入新数据。

要注意的是：插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。

相当于：

if not exists (select 1 from t where id = 1) 
insert into t(id, update_time) values(1, getdate()) 
else 
update t set update_time = getdate() where id = 1；

使用 REPLACE INTO，必须拥有表的 INSERT 和 DELETE 权限。

REPLACE 语句会返回一个数，来知识受影响的行的数目。该数是被删除和被插入的行数的和。如果对于一个单行 REPLACE 该数为1，则一行被插入，同时没有行被删除。如果该数大于1，则在新行被插入前，有一个或多个旧行被删除。如果表包含多个唯一索引，并且新行复制了在不同的唯一索引中的不同旧行的值，则有可能是一个单一行替换了多个旧行。

受影响的行数可以容易得确定是否REPLACE 只添加了一行，或者是否REPLACE也替换了其它行：检查该数是否为1 （添加）或者更大 （替换）。

目前，您不能在一个子查询中，向一个表中更换，同时从同一个表中选择。

以下是所用算法的更详细的说明（该算法也用于LOAD DATA…REPLACE）：

1. 尝试把新行插入到表中
2. 当因为对于主键或唯一关键字出现重复关键字错误而造成插入失败时：
   • a. 从表中删除含有重复关键字值得冲突行
   • b. 再次尝试把新行插入到表中

三种形式：

1. replace into tbl_name(col_name, ...) values(...)
2. replace into tbl_name(col_name, ...) select ...
3. replace into tbl_name set col_name=value, ...

PS：

mysql 中常用的三种插入数据的语句：

• insert into 表示插入数据，数据库会检查主键，如果出现重复会报错；
• replace into 表示插入替换数据，需求表中有 Primary Key，或者 unique 索引，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和 insert into 一样；
• insert ignore 表示，如果表中已经存在相同的记录，则忽略当前新数据；

3. where 1 = 1 解释

select * from table where 1=1，其中where 1=1，由于1=1永远是成立的返回true，条件为真，所以这条语句就相当于 select * from table，返回查询表中的所有数据，并不会影响查询速度和引起 sql 注入。

使用 where 1=1 的场景：

String sqlStr = "select * from table where 1=1";
if(user.getUsername()!=""){
sqlStr = sqlStr + "username=" + user.getUsername();
}
if(user.getPassword()!=""){
sqlStr = sqlStr + "and password=" + user.getPassword();
}

上述sql的言下之意是：加上 1 = 1 可以应付多变的查询条件，仅仅只是为了满足多条件查询羊肉面中不确定的各种因素而采用的一种构造一条正确能运行的动态SQL语句的一种方法，并没有其他影响。

mybatis 框架可以通过 where 标签来代替 1=1 的作用。

<select id="getUserInfo" resultType="map">
		SELECT username,sex
		FROM user
		<where>
			<if test=" username!='' and username!= null ">
	         username = #{username}
			</if>
			<if test=" sex!='' and sex!= null ">
	         AND sex = #{sex}
			</if>
		</where>
</select>

where 标签在满足一个及以上的if 条件有值的情况下才去插入”WHERE“子句，且若最后的内容是”AND“或者”OR“开头的，where 标签也知道如何将他们去除。