mysql 抹掉数值_【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，两者对比如下

truncate 只能用于表，delete 可以用做表和视图

delete语句为DML，这个操作会被放到 rollback segment中,事务提交后才生效。如果有 trigger,执行的时候也会触发。

truncate DDL 操作立即生效，原数据不放到 rollback segment中，不能回滚。

有 FOREIGN KEY 的表不能执行 truncate，truncate 不返回影响的行数，truncate 执行后表中所有 AUTO_INCREMENT 的行会重置为 0，truncate 不记录到日志不触发 trigger 所以速度比 delete 快很多，但是我们要谨慎使用 truncate