MySQL之删除数据

单表删除语法

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

多表删除

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

使用修饰符LOW_PRIORITY时 SQL 执行优先级会很低直到所有查询语句执行完毕才执行。LOW_PRIORITY 只对只支持表锁的引擎有用，例如 MyISAM、MEMORY、MERGE。
使用修饰符QUICK 时，存储引擎不会去处理因删除数据而需要合并索引叶子节点，这样可以在一定程度上加快删除速度，例如 MyISAM。
修饰符IGNORE 用来忽略 SQL 执行过程中的错误报错。
另外 delete 语句支持 order by 和 limit 来删除满足 where 的部分数据。

删除单表例子

根据id删除一个学生

mysql> delete from students where id=1;
Query OK, 1 row affected (0.00 sec)

使用 order by limit 删除 id 最大的学生

mysql> delete from students order by id desc limit 1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from students;
+----+------+--------+-----+------+---------------------+---------------------+
| id | no   | name   | sex | age  | created_at          | updated_at          |
+----+------+--------+-----+------+---------------------+---------------------+
|  2 | 0002 | 冷锋   |   2 |   25 | 2019-12-20 20:00:00 | 2019-12-20 20:00:00 |
|  3 | 0003 | 六一   |   2 |   25 | 2019-12-20 20:00:00 | 2019-12-20 20:00:00 |
|  4 | 0004 | 杨过   |   2 |   25 | 2019-12-20 00:26:14 | NULL                |
|  6 | 0006 | 铁蛋   |   2 |   25 | 2019-12-20 01:51:46 | NULL                |
+----+------+--------+-----+------+---------------------+---------------------+
4 rows in set (0.00 sec)

删除多表的例子

新建一个作业表并初始化一部分数据

mysql> CREATE TABLE `homeworks` (
    ->   `id` int NOT NULL AUTO_INCREMENT COMMENT '作业id',
    ->   `student_id` int NOT NULL COMMENT '学生学号',
    ->   `name` varchar(128) NOT NULL COMMENT '作业名字',
    ->   `status` tinyint NOT NULL DEFAULT '0' COMMENT '0 未做 1 已提交 2 已批改',
    ->   `content` text DEFAULT NULL COMMENT '作业内容',
    ->   `score` int not null default 0 comment '作业分数',
    ->   `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    ->   `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    ->   PRIMARY KEY (`id`),
    ->   index `student_id_index` (`student_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='作业表';
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql>
mysql>
mysql> insert into homeworks (`student_id`,`name`) values(3,'1+1'),(3,'2+2'),(2,'1+1');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from homeworks;
+----+------------+------+--------+---------+-------+---------------------+------------+
| id | student_id | name | status | content | score | created_at          | updated_at |
+----+------------+------+--------+---------+-------+---------------------+------------+
|  1 |          3 | 1+1  |      0 | NULL    |     0 | 2019-12-29 22:39:57 | NULL       |
|  2 |          3 | 2+2  |      0 | NULL    |     0 | 2019-12-29 22:39:57 | NULL       |
|  3 |          2 | 1+1  |      0 | NULL    |     0 | 2019-12-29 22:39:57 | NULL       |
+----+------------+------+--------+---------+-------+---------------------+------------+

删除 id=3 的学生及其所有作业

mysql> delete s,h from students s left join homeworks h on s.id=h.student_id where s.id=3;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from students;
+----+------+--------+-----+------+---------------------+------------+
| id | no   | name   | sex | age  | created_at          | updated_at |
+----+------+--------+-----+------+---------------------+------------+
|  4 | 0004 | 杨过   |   2 |   25 | 2019-12-20 00:26:14 | NULL       |
|  6 | 0006 | 铁蛋   |   2 |   25 | 2019-12-20 01:51:46 | NULL       |
+----+------+--------+-----+------+---------------------+------------+
2 rows in set (0.00 sec)

mysql> select * from homeworks;
+----+------------+------+--------+---------+-------+---------------------+------------+
| id | student_id | name | status | content | score | created_at          | updated_at |
+----+------------+------+--------+---------+-------+---------------------+------------+
|  1 |          1 | 1+1  |      0 | NULL    |     0 | 2019-12-29 22:39:57 | NULL       |
|  2 |          1 | 2+2  |      0 | NULL    |     0 | 2019-12-29 22:39:57 | NULL       |
+----+------------+------+--------+---------+-------+---------------------+------------+
2 rows in set (0.00 sec)

truncate

我们有时候需要清除数据库数据，我们可以使用 delete 也可以用 truncate，两者对比如下

1. truncate 只能用于表，delete 可以用做表和视图
2. delete语句为DML，这个操作会被放到 rollback segment中,事务提交后才生效。如果有 trigger,执行的时候也会触发。
3. truncate DDL 操作立即生效，原数据不放到 rollback segment中，不能回滚。
4. 有 FOREIGN KEY 的表不能执行 truncate，truncate 不返回影响的行数，truncate 执行后表中所有 AUTO_INCREMENT 的行会重置为 0，truncate 不记录到日志不触发 trigger 所以速度比 delete 快很多，但是我们要谨慎使用 truncate