Mysql系列一 你可能不知道的 CRUD

凡是过去,皆为序曲。
All the past, all is overture.

本系列旨在系统学习提升Mysql技能,更完整内容可以参考阿里新零售数据库设计与实战

DB引擎

你可能不知道的CRUD

INSERT

情况一 Duplicate key

当批量更新,如果有重复的primary key,如果有一条失败,则全部失败。

更新时忽略错误行,继续执行该如何处理?

如: t_dept 表中已经存在 deptno = 40 的数据;

步骤一 直接插入
INSERT INTO t_dept (deptno, dname, loc) VALUES 
(40, '企划部', '北京'),
(50, '培训部', '上海'),

错误信息

Error occurred during SQL query execution
Reason:
SQL Error [1062] [23000]: Duplicate entry '40' for key 't_dept.PRIMARY'
步骤二 关键字–IGNORE
INSERT IGNORE  INTO t_dept (deptno, dname, loc) VALUES 
(40, '企划部', '北京'),
(50, '培训部', '上海')

情况二 upsert操作

目标:存在则更新,不存在则创建。

CREATE TABLE `t_emp_ip`  (
`id` int(11) NOT NULL,
`empno` int(11) NOT NULL,
`ip` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `empno`(`empno`) USING BTREE,
UNIQUE INDEX `ip`(`ip`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- 原有数据
-- empno字段为: 唯一性约束
id|empno|ip |
--|-----|-----------|
1| 100|192.168.0.1|
2| 101|192.168.0.2|

目标

  1. 插入102、103两条数据
  2. 更新101对应的IP
步骤一: 错误示范
INSERT INTO t_emp_ip(id, empno, ip) VALUES
(4, 102, '192.168.0.100'),
(5, 103, '192.168.0.150'),
(6, 101, '192.168.0.200')
Error occurred during SQL query execution
Reason:
SQL Error [1062] [23000]: Duplicate entry '101' for key 't_emp_ip.empno'
步骤二 正确操作
INSERT INTO t_emp_ip(id, empno, ip) VALUES
(4, 102, '192.168.0.100'),
(5, 103, '192.168.0.150'),
(6, 101, '192.168.0.200')
-- 子句更新||插入
ON duplicate UPDATE ip=VALUES(ip);

结果

id|empno|ip           |
--|-----|-------------|
1| 100|192.168.0.1 |
2| 101|192.168.0.200| -- update
4| 102|192.168.0.100| -- insert
5| 103|192.168.0.150| -- insert
步骤三 测试其他表

如果需要更新多个字段,则字句中需要罗列完整的字段。

INSERT INTO t_emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES
(8000, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, 0.9 ,10);

--

INSERT INTO t_emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES
(8000, 'MILLER', 'CLERK', 7782, '1982-01-23', 300.00, 100 ,0)
ON duplicate KEY UPDATE comm=VALUES(comm);

子查询

相关子查询就是要循环执行多次的子查询。

  1. 子查询:独立于外部查询的子查询,子查询总共执行一次,执行完毕后将值传递给外部查询;
  2. 相关子查询:相关子查询的执行依赖于外部查询的数据,外部查询执行一行,子查询就执行一次;

注:

  1. mysql默认关闭了缓存,所以每个子查询都是相关子查询
  2. 一般ORM开启了缓存。
SELECT empno, ename
FROM
t_emp
WHERE
sal > (
-- WHERE语句每过滤一条数据,子查询就会执行一次
SELECT sal FROM t_emp WHERE empno = 7499
)
AND empno != 7499;

代替子查询

使用FROM子查询,代替WHERE子查询
  1. FROM子查询只会执行一次,优先执行数据来源,所以不是相关子查询
-- 相关子查询代替方案
explain SELECT empno, ename
FROM t_emp e
JOIN (SELECT sal FROM t_emp WHERE empno = 7499) t
ON e.sal > t.sal AND e.empno != 7499;

表连接

内连接

内连接里,查询条件写在ON子句或where子句,效果相同

SELECT e.ename, e.dname 
FROM t_emp e
JOIN t_dept d ON e.deptno = d.deptno AND d.deptno = 10;

SELECT e.ename, d.name
FROM t_emp e
JOIN t_dept d ON e.deptno = d.deptno
WHERE d.deptno = 10;

外连接

内连接里,查询条件写在ON子句where子句效果相差很大.

-- 保留左表所有数据和右表关联
SELECT e.ename, e.ename
FROM t_emp e
-- ON 条件如果符合则返回,否则返回null
LEFT JOIN t_dept d ON e.deptno = d.deptno
-- 不强求连接条件,会返回部门ID不等10的部门
AND d.deptno = 10;

返回结果

ename |ename |deptno|
------|------|------|
SMITH |SMITH | 20|
ALLEN |ALLEN | 30|
WARD |WARD | 30|
JONES |JONES | 20|
MARTIN|MARTIN| 30|
BLAKE |BLAKE | 30|
CLARK |CLARK | 10|
...
-- 左表数据必须满足 where 条件才会返回。
SELECT e.ename, d.name
FROM t_emp e
LEFT JOIN t_dept d ON e.deptno = d.deptno
WHERE d.deptno = 10;

返回结果

ename |ename |deptno|
------|------|------|
CLARK |CLARK | 10|
KING |KING | 10|
MILLER|MILLER| 10|
MILLER|MILLER| 10|

小结

  1. where子句:需要满足条件才可以返回;
  2. on子句:完全以左表为主,

UPDATE

表连接修改

UPDATE t_emp SET sal = 10000
WHERE deptno = (SELECT deptno FROM t_dept WHERE dname = 'SALES');

UPDATE t_emp e
-- 内连接,条件写在on || where 效果一样。
JOIN t_dept d ON e.deptno = d.deptno AND d.dname = 'SALES'
-- 更改多个表的字段
SET e.sal = 10000, d.dname = '销售部';

表连接删除

-- 删除t_emp、t_dept表符合条件的数据
-- 写谁删谁,不写不删除
DELETE e, d FROM t_emp e JOIN t_dept d ON e.deptno = d.deptno AND d.dname = '销售部';

事务机制

为啥数据库需要有事务机制?

类比,操作重要代码,可能先要新建一个分之,避免混乱。

同理:如果数据的写入直接操作数据文件是非常危险的事情

如:给员工工资普普涨,但中途失败了,怎么确认那些成功,哪些失败。

就需要引入事务机制。

日志文件相当于数据副本

undo & redo

数据库日志分类

  1. 重做日志(redo log)
  2. 回滚日志(undo log)
  3. 二进制日志(binlog)
  4. 错误日志(errorlog)
  5. 慢查询日志(slow query log)
  6. 一般查询日志(general log)
  7. 中继日志(relay log)
  1. SQL操作的记录会被复制undo日志中;
  2. CRUD的结果会记录在redo日志中;
  3. 如果CRUD没问题,将redo日志中的数据同步到SQL文件中;
  4. 如果同步过程中出现问题,则之后再次同步即可。

undo & redo 对于事务机制的重要性不言而喻。

事务机制

事务是一个或多个SQL语句组成的整体,要么全部执行成功,要么全部执行失败。

ACID

  1. 原子性:要么成功要么失败;
  2. 一致性:不论并发多少,必须保证结果一致性;事务隔离,不能读写其他事务的临时日志;
  3. 隔离性:事务相互之间隔离;
  4. 持久性:一旦提交,结果便是永久性的,宕机可以恢复事务日志完成数据的持久化。

参考

阿里新零售数据库设计与实战
Mysql-innoDB存储引擎(事物,锁,MVCC)
浅谈 MySQL 子查询及其优化
Explain语法
步步深入:MySQL架构总览->查询执行流程->SQL解析顺序
MySQL到底有多少种日志类型需要我们记住的!