MySQL多表关联删除

发表于 2020-03-17 更新于 2023-03-01 分类于 MySQL
本文字数： 75 阅读时长 ≈ 1 分钟

t2和t3数据表中存有他t1表中数据的id

选择删除t1表中的数据以及t2和t3表中相关（task_id）的数据

SQL语句如下：

DELETE t1, t2, t3
FROM t_satisfaction_followup_patients t1
LEFT JOIN t_hospital_followup_record t2 ON t1.id = t2.relation_id
LEFT JOIN t_manage_call t3 ON t1.id = t3.task_id
WHERE t1.id = '8aa5eeb2529d46d5962d561d3a9beedc'

MySQL表历史数据转移备份

发表于 2020-03-13 更新于 2023-03-01 分类于 MySQL
本文字数： 143 阅读时长 ≈ 1 分钟

每天4点将4天前的数据转移到历史表，减少业务表的压力。

确认MySQL计划任务是否开启

查看定时任务状态

show variables like '%event_sche%'
开启定时任务

set global event_scheduler = 1
关闭定时任务

set global event_scheduler = 1

创建存储过程（即：函数）

CREATE DEFINER=`root`@`localhost` PROCEDURE `dtl_move_his`( )
BEGIN
/*备份4天之前的数据*/
INSERT INTO `dtl_his` SELECT
*
FROM
    `dtl` AS d 
WHERE
    DATE( d.create_time ) <= DATE_SUB( CURDATE(), INTERVAL + 4 DAY );
/*删除4天之前的数据*/
DELETE 
FROM
    `dtl` AS d
WHERE
    DATE( d.create_time ) <= DATE_SUB( CURDATE(), INTERVAL + 4 DAY );
END

创建计划任务（即：事件）

MySQL自定义排序

发表于 2020-03-11 更新于 2023-03-01 分类于 MySQL
本文字数： 689 阅读时长 ≈ 3 分钟

在 SQL 查询中可能有时需要使用 ASC 或 DESC 或使用特殊排序字段才能完成的特定顺序. MySQL 有一个 ORDER BY FIELD 函数可以用来做这个.

测试数据

本文中的示例数据使用我的示例 fruit 表. 这是一个有点简单的表, 但它可以用来很好地说明这篇文章中的观点.

按特定字段值排序

fruit 表有一个 name 字段, 具有以下特定的值: 苹果(Apple), 香蕉(Banana), 橘子(Orange), 梨(Pear). 每个特定的值都有一系列的品种.

比方说, 为了论证的缘故, 我们要按香蕉, 苹果, 梨, 橘子等特定的顺序排列数据, 然后再按品种排序. 使用普通的 ORDER BY 子句不可能这样做, 因为这个字段的升序或降序排序不起作用. 我们要么需要某种形式的排序列或进行其他选择.

在 ORDER BY 子句中使用 FIELD 函数可以实现这一点. 它的工作方式是指定要排序的列, 然后按顺序排序它们的值. 例如:

1 2	SELECT * FROM fruit ORDER BY FIELD(name, 'Banana', 'Apple', 'Pear', 'Orange'), variety;

来自示例表的结果数据如下所示:

+----------+--------+---------------------+
| fruit_id | name   | variety             |
+----------+--------+---------------------+
|       11 | Banana | Burro               |
|       12 | Banana | Cavendish           |
|       10 | Banana | Plantain            |
|        6 | Apple  | Cox's Orange Pippin |
|        7 | Apple  | Granny Smith        |
|        1 | Apple  | Red Delicious       |
|        8 | Pear   | Anjou               |
|        4 | Pear   | Bartlett            |
|        2 | Pear   | Comice              |
|        5 | Orange | Blood               |
|        3 | Orange | Navel               |
|        9 | Orange | Valencia            |
+----------+--------+---------------------+

疑难杂症

使用此功能时有一个小小的疑难杂症. 列中不在 FIELD 函数中的任何值将在指定的值之前或多或少随机出现. 例如, 只指定苹果和香蕉:

1 2	SELECT * FROM fruit ORDER BY FIELD(name, 'Banana', 'Apple') DESC, variety;

这导致:

+----------+--------+---------------------+
| fruit_id | name   | variety             |
+----------+--------+---------------------+
|        6 | Apple  | Cox's Orange Pippin |
|        7 | Apple  | Granny Smith        |
|        1 | Apple  | Red Delicious       |
|       11 | Banana | Burro               |
|       12 | Banana | Cavendish           |
|       10 | Banana | Plantain            |
|        8 | Pear   | Anjou               |
|        4 | Pear   | Bartlett            |
|        5 | Orange | Blood               |
|        2 | Pear   | Comice              |
|        3 | Orange | Navel               |
|        9 | Orange | Valencia            |
+----------+--------+---------------------+

解决问题的方法

虽然通常只有在确切的列已知的情况下才使用此函数, 但解决方法是颠倒指定字段的顺序并按降序对其排序, 然后在同一个字段上进行第二次排序.

下面的例子, 不管它看起来如何，实际上按照香蕉, 苹果, 然后按照升序排列:

1 2	SELECT * FROM fruit ORDER BY FIELD(name, 'Apple', 'Banana') DESC, name, variety;

这导致:

+----------+--------+---------------------+
| fruit_id | name   | variety             |
+----------+--------+---------------------+
|       11 | Banana | Burro               |
|       12 | Banana | Cavendish           |
|       10 | Banana | Plantain            |
|        6 | Apple  | Cox's Orange Pippin |
|        7 | Apple  | Granny Smith        |
|        1 | Apple  | Red Delicious       |
|        5 | Orange | Blood               |
|        3 | Orange | Navel               |
|        9 | Orange | Valencia            |
|        8 | Pear   | Anjou               |
|        4 | Pear   | Bartlett            |
|        2 | Pear   | Comice              |
+----------+--------+---------------------+