MySQL 开发要点

落日映苍穹つ 2022-07-13 14:13 152阅读 0赞

本文从开发层面全面梳理 MySQL 各个开发要点，以帮助大家准确理解、熟悉掌握相关技术。简要讲解 Table/View等各种数据库对象，并重点讲解 SELECT/INSERT/DELETE/UPDATE 等使用频率最高的语句，最后讲解了存储过程和记录去重的综合实战。  
  
**作者：**王克锋  
**出处：**[https://kefeng.wang/2016/12/18/mysql-development/][https_kefeng.wang_2016_12_18_mysql-development]  
**版权：**[自由转载-非商用-非衍生-保持署名][-_-_-]，转载请标明作者和出处。

## 1.概述 ##

本文的目的在于，和大家一起梳理 MySQL 最常用的开发要点，以`查漏补缺并准确理解，能信手拈来灵活运用`。起因是笔者在面试应聘者、与周边同事沟通时，发现一些开发者对 MySQL 的某些常用语法似懂非懂，实际操作时无从下手。  
　　笔者以为，`最基础最常用的，才是最需要熟练掌握的`，然后再去学更高级的内容。

## 2.数据定义语言(DDL) ##

下面列出的 View/Function/Procedure/Trigger, 这些机制给我们开发者带来很大的方便，但它们对数据库性能有影响，而数据库性能经常成为系统中的瓶颈。所以，如果对性能要求高，那么数据库中应该只保存数据，逻辑处理尽量移到应用程序中进行。

### 2.1 Database ###

SHOW DATABASES; ## 当前主机数据库列表
    DROP DATABASE IF EXISTS db; ## 删除数据库[如果存在]
    CREATE DATABASE IF NOT EXISTS db CHARACTER SET UTF8; ## 创建数据库[如果不存在]
    USE db; ## 切换db为默认数据库

### 2.2 Table ###

SHOW TABLES; ## 当前数据库中数据表
    
    TRUNCATE TABLE db.users; ## 彻底清空数据表（清除碎片）
    DROP TABLE IF EXISTS db.users; ## 删除数据表[如果存在]
    CREATE TABLE IF NOT EXISTS db.users( ## 新建数据表[如果不存在] id INT AUTO_INCREMENT COMMENT '用户ID', name VARCHAR(32) NOT NULL COMMENT '用户名', city VARCHAR(16) NOT NULL COMMENT '城市', birthday DATE DEFAULT '2000-01-01' COMMENT '生日', created TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '生成时间', updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY(id), UNIQUE uni_users_name(name), INDEX idx_users_city(city), INDEX idx_users_birthday(birthday) ) ENGINE=INNODB DEFAULT CHARSET UTF8 COMMENT '用户表';

INSERT INTO db.users(name, city, birthday) VALUES ## 没指定的列，取值为默认值 ('张三', '北京', '2000-01-01'), ## id=1 ('李四', '上海', '2003-01-01'), ## id=2 ('王五', '广州', '2006-01-01'); ## id=3
    SELECT * FROM db.users ORDER BY id;

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
   <th>city</th> 
   <th>birthday</th> 
   <th>created</th> 
   <th>updated</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>张三</td> 
   <td>北京</td> 
   <td>2000-01-01</td> 
   <td>2016-08-21 20:08:15</td> 
   <td>2016-08-21 20:08:15</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>李四</td> 
   <td>上海</td> 
   <td>2003-01-01</td> 
   <td>2016-08-21 20:08:15</td> 
   <td>2016-08-21 20:08:15</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>王五</td> 
   <td>广州</td> 
   <td>2006-01-01</td> 
   <td>2016-08-21 20:08:15</td> 
   <td>2016-08-21 20:08:15</td> 
  </tr> 
 </tbody> 
</table>

CREATE TABLE IF NOT EXISTS db.orders( id INT AUTO_INCREMENT COMMENT '订单ID', uid INT NOT NULL COMMENT '用户ID', amount DECIMAL(10,2) NOT NULL COMMENT '金额', created TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '生成时间', updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY(id), INDEX idx_orders_uid(uid), INDEX idx_orders_amount(amount) ) ENGINE=INNODB DEFAULT CHARSET UTF8 COMMENT '订单表';
    INSERT INTO db.orders(uid, amount) VALUES (1, 100), (1, 200), (2, 400), (9, 500);
    SELECT * FROM db.orders ORDER BY id;

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>uid</th> 
   <th>amount</th> 
   <th>created</th> 
   <th>updated</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>1</td> 
   <td>100.00</td> 
   <td>2016-08-21 20:10:28</td> 
   <td>2016-08-21 20:10:28</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>1</td> 
   <td>200.00</td> 
   <td>2016-08-21 20:10:28</td> 
   <td>2016-08-21 20:10:28</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>2</td> 
   <td>400.00</td> 
   <td>2016-08-21 20:10:28</td> 
   <td>2016-08-21 20:10:28</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>9</td> 
   <td>500.00</td> 
   <td>2016-08-21 20:10:28</td> 
   <td>2016-08-21 20:10:28</td> 
  </tr> 
 </tbody> 
</table>

CREATE TABLE db.users_bak1 LIKE db.users;
    CREATE TABLE db.users_bak2 SELECT * FROM db.users WHERE city IN ('北京','上海') ORDER BY id;
    
    DESC db.users; ## 查看表结构
    SHOW CREATE TABLE db.users; ## 查看建表语句
    
    ## 临时表（建在内存中，当前会话可见）
    DROP TEMPORARY TABLE IF EXISTS db.tmp_users;
    CREATE TEMPORARY TABLE IF EXISTS db.tmp_users ... ## 修改表定义 ALTER TABLE db.users ADD temp VARCHAR(32) COMMENT '临时列', CHANGE temp temp VARCHAR(64) COMMENT '临时列2', ADD INDEX idx_users_temp(temp), DROP temp;

## 分区表
    CREATE TABLE IF NOT EXISTS db.users(...) ENGINE=INNODB DEFAULT CHARSET UTF8 COMMENT '用户表' PARTITION BY RANGE COLUMNS(birthday) ( PARTITION Y1960 VALUES LESS THAN ('1960-01-01'), PARTITION Y1970 VALUES LESS THAN ('1970-01-01'), PARTITION Y1980 VALUES LESS THAN ('1980-01-01'), PARTITION Y1990 VALUES LESS THAN ('1990-01-01'), PARTITION Y2000 VALUES LESS THAN ('2000-01-01'), PARTITION Y2010 VALUES LESS THAN ('2010-01-01'), PARTITION YMAXV VALUES LESS THAN MAXVALUE );

### 2.3 View ###

CREATE OR REPLACE VIEW db.view_users AS SELECT id,name,city FROM db.users WHERE city IN ('北京','上海') ORDER BY id DESC;
    SELECT * FROM db.view_users;

### 2.4 Function ###

DROP FUNCTION IF EXISTS db.func_users;
    
    DELIMITER $$ ## 设置定界符
    CREATE FUNCTION db.func_users(v_id INT) RETURNS VARCHAR(32) BEGIN DECLARE v_name VARCHAR(32) DEFAULT NULL;
        SELECT name INTO v_name FROM users WHERE id=v_id;
        RETURN v_name;
    END$$ DELIMITER ; ## 恢复定界符
    
    SELECT db.func_users(1); ## 张三

### 2.5 Procedure ###

DROP PROCEDURE IF EXISTS db.proc_users;
    
    DELIMITER $$ ## 设置定界符
    CREATE PROCEDURE db.proc_users(IN v_id INT, OUT v_name VARCHAR(32)) BEGIN SELECT name INTO v_name FROM users WHERE id=v_id;
    END$$ DELIMITER ; ## 恢复定界符
    
    SET @name=NULL;
    CALL db.proc_users(1, @name);
    SELECT @name; ## 张三

### 2.6 Trigger ###

DROP TRIGGER IF EXISTS db.trig_users;
    
    DELIMITER $$ ## 设置定界符
    CREATE TRIGGER db.trig_users AFTER UPDATE ON db.users FOR EACH ROW BEGIN INSERT INTO db.users_bak1(id, NAME, city, birthday, created, updated) VALUES(OLD.id, OLD.name, OLD.city, OLD.birthday, OLD.created, OLD.updated);
        INSERT INTO db.users_bak2(id, NAME, city, birthday, created, updated) VALUES(NEW.id, NEW.name, NEW.city, NEW.birthday, NEW.created, NEW.updated);
    END$$ DELIMITER ; ## 恢复定界符

## 3.数据操纵语言(DML) ##

### 3.1 SELECT 语句（难点） ###

#### 3.1.1 基本 ####

SELECT id, name, city, YEAR(CURDATE())-YEAR(birthday) AS age FROM db.users WHERE (name LIKE '张%' AND city IN ('北京', '上海')) OR birthday BETWEEN '1990-01-01' AND '2009-12-31' ORDER BY id DESC LIMIT 0,3; ## [m,]n

#### 3.1.2 统计 ####

SELECT MIN(birthday), MAX(birthday), COUNT(*) ## AVG(), SUM() FROM db.users WHERE city IN ('北京', '上海') ## 分组前筛选 GROUP BY city ## 分组依据 HAVING COUNT(*)>0 ## 分组后筛选 ORDER BY id DESC LIMIT 0,3;

#### 3.1.3 子表 ####

SELECT t.*, (SELECT birthday FROM db.users WHERE NAME='李四') AS birthday4 FROM (SELECT id, NAME, city, birthday FROM db.users WHERE id>1) AS t WHERE t.birthday>(SELECT birthday FROM db.users WHERE NAME='张三') AND EXISTS(SELECT 1 FROM db.orders AS o WHERE o.uid=t.id);

#### 3.1.4 JOIN（难点中的难点） ####

##### 3.1.4.1 常规关联 #####

左右表严格关联上的记录才返回。  
特别注意：无关联条件时，结果为m\*n条记录，切记不要漏写关联条件。

SELECT u.name, o.id AS oid, o.amount FROM db.users AS u, db.orders AS o WHERE u.id=o.uid;

<table> 
 <thead> 
  <tr> 
   <th>oid</th> 
   <th>amount</th> 
   <th>name</th> 
   <th>city</th> 
   <th>birthday</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>100.00</td> 
   <td>张三</td> 
   <td>北京</td> 
   <td>2000-01-01</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>200.00</td> 
   <td>张三</td> 
   <td>北京</td> 
   <td>2000-01-01</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>400.00</td> 
   <td>李四</td> 
   <td>上海</td> 
   <td>2003-01-01</td> 
  </tr> 
 </tbody> 
</table>

##### 3.1.4.2 左连接 #####

除了严格关联上的记录，“左表”存在而右表不存在的记录也返回。

SELECT o.id AS oid, o.amount, u.name, u.city, u.birthday FROM db.users AS u LEFT JOIN db.orders AS o ON u.id=o.uid;
        ## WHERE o.id > 2;

##### 3.1.4.3 右连接 #####

除了严格关联上的记录，“右表”存在而左表不存在的记录也返回。

SELECT o.id AS oid, o.amount, u.name, u.city, u.birthday FROM db.users AS u RIGHT JOIN db.orders AS o ON u.id=o.uid;

左关联的实际应用：统计每个用户的订单个数和总金额。  
如果用普通连接，则无订单用户将不用列出。

SELECT u.id, u.name, COUNT(o.id) AS order_number, IFNULL(SUM(o.amount),0) AS total_amoumt FROM db.users AS u LEFT JOIN db.orders AS o ON u.id=o.uid GROUP BY u.id;

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
   <th>order_number</th> 
   <th>total_amoumt</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>张三</td> 
   <td>2</td> 
   <td>300.00</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>李四</td> 
   <td>1</td> 
   <td>400.00</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>王五</td> 
   <td>0</td> 
   <td>0.00</td> 
  </tr> 
 </tbody> 
</table>

#### 3.1.5 UNION \[ALL\] ####

*  各个子查询的字段的个数和类型必须一致；
 *  UNION 自动去重，UNION ALL 不去重。

SELECT * FROM db.users WHERE city='北京' UNION SELECT * FROM db.users_bak1 WHERE city='上海' UNION SELECT * FROM db.users_bak2 WHERE city='广州';

#### 3.1.6 索引 ####

索引缺点: 占空间；`INSERT/DELETE/UPDATE` 时慢(要更新索引区)。  
有用子句: `WHERE, ORDER BY, GROUP BY, JOIN`  
索引禁忌: `YEAR(birthday)=2016, INSTR(city,'上')>0, name LIKE '%ABC%'`  
注意: 每次执行新写的SELECT语句，务必考虑相关数据表的数据量、是否能用到索引，可考查`WHERE, ORDER BY, GROUP BY, JOIN`等子句，或者使用`EXPLAIN`查看执行计划。

### 3.2 INSERT ###

INSERT INTO users(nick,city) VALUES ('张三', '北京'), ('李四', '上海'), ('王五', '广州'); ## 没指定的列，取值为默认值
    INSERT INTO tmp_users(nick,city) SELECT nick, city FROM tmp_users;

**唯一索引重复时的处理方法**

INSERT INTO users(nick,city) VALUES('张三', '上海'); ## 默认用法，会报错
    INSERT IGNORE INTO users(nick,city) VALUES('张三', '上海'); ## 冲突时忽略新记录
    INSERT INTO users(nick,city) VALUES('张三', '上海') ON DUPLICATE KEY UPDATE city=VALUES(city); ## 覆盖原记录，未涉及列保持原值
    REPLACE INTO users(nick,city) VALUES('张三', '上海'); ## 覆盖原记录，未涉及列全部更新为默认值

### 3.3 DELETE ###

DELETE FROM db.users WHERE id=8;

切忌 `WHERE` 条件有漏写而多删除数据！首次执行语句前，可改为 SELECT 确认涉及的数据；

### 3.4 UPDATE ###

UPDATE db.users SET updated=NOW() WHERE id<5;

切忌 `WHERE` 条件有漏写而多删除数据！首次执行语句前，可改为 `SELECT` 确认涉及的数据；

## 4.存储过程 ##

### 4.1 使用游标 ###

CREATE PROCEDURE db.proc_users(IN vi_id INT) BEGIN DECLARE v_id INT;
        DECLARE v_name VARCHAR(32);
        DECLARE v_logs VARCHAR(255) DEFAULT '';
    
        DECLARE done BOOLEAN DEFAULT FALSE;
        DECLARE cur_users CURSOR FOR (SELECT id,NAME FROM db.users WHERE id<=vi_id);
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
        OPEN cur_users;
        START TRANSACTION;
        REPEAT
            FETCH cur_users INTO v_id, v_name;
            IF !done THEN
                SET v_logs = CONCAT(v_logs, v_id, ',');
                UPDATE db.users SET updated=NOW() WHERE id=v_id;
            END IF;
        UNTIL done END REPEAT;
        COMMIT; ## ROLLBACK;
        CLOSE cur_users;
    
        SELECT v_logs; ## 可返回记录集，用于调试或Java程序
    END$$

### 4.2 调用方法 ###

CALL db.proc_users(12);

### 4.3 优化原则 ###

*  确保使用到索引；
 *  多条记录批量处理；
 *  多条记录批量提交。

## 5.综合实战（重复记录去重） ##

假设有张成绩数据表scores，每个学生的每个课程都有且只有一条记录，主要字段有：id(成绩ID) / sid(Student ID，学生ID) / cid(course ID, 课程ID) / score(成绩)。但现在某些学生的某些课程有多条记录，希望去除重复记录。

### 5.1 重复的组合，生成统计表 ###

挑选有重复的 \[sid, cid\] 组合，存储于“统计表”， \#\# HAVING COUNT(1)>1  
对于每个组合，记下其中最小的 id, 清除时要保留该id的记录 \#\# MIN(id)

INSERT INTO tmp_scores_stat(sid, cid, id_min, id_cnt) SELECT sid, cid, MIN(id), COUNT(1) FROM scores GROUP BY sid, cid HAVING COUNT(1)>1;

### 5.2 多余的记录，生成备份表 ###

如果将要删除的记录，日后需要使用，则需要此步备份，否则此步可省掉。

CREATE TABLE tmp_scores_extra AS SELECT s.* FROM scores s WHERE EXISTS ( SELECT 1 FROM tmp_scores_stat t WHERE t.sid=s.sid AND t.cid=s.cid AND t.id_min!=s.id); ## 每个组合中，保留id最小的那条记录

### 5.3 删除原表的多余记录 ###

DELETE FROM scores WHERE EXISTS ( SELECT 1 FROM tmp_scores_extra t WHERE t.id=scores.id);

### 5.4 注意事项 ###

*  如果是对在线数据库操作，5.1 的大表统计很耗时，容易导致长时间锁表，应该按id段拆分成多批统计，最后再汇总；
 *  由于涉及数据量大，每步务必用到索引，避免全表扫描；
 *  增删改语句执行前，务必核对操作的数据准确无误，可先改为SELECT语句确认记录，至少确认记录条数一致。

[https_kefeng.wang_2016_12_18_mysql-development]: https://kefeng.wang/2016/12/18/mysql-development/
[-_-_-]: https://creativecommons.org/licenses/by-nc-nd/3.0/deed.zh