MySQL 管理运维:这类 VALUES() 已被弃用

2024年 3月 24日 47.1k 0

在之前的文章 MySQL 管理运维:妙用 DATETIME 数据类型的默认值 中,介绍了如何巧用自动更新字段时间戳。

本文将继续扩展该案例,使用 INSERT ... ON DUPLICATE KEY UPDATE 语句来检查记录用户登录情况。

背景

用户登录数据库的时间、次数,执行的语句,都是重要的审计项目。在老版本的 MySQL 中没有记录用户密码最后更改时间和用户最后登录时间,需要依靠外挂工具实现。

在 MySQL 8 中,系统表 mysql.userpassword_last_changed 字段记录了密码最后更改时间,但用户最后登录时间仍较难查询。

好在,performance_schema 库中的 users 表记录了所有用户的总连接次数。

users 表为连接到 MySQL 服务器的每个用户包含一行。对于每个用户名,该表计算当前连接数和总连接数。 表大小在服务器启动时自动调整大小。要显式设置表大小,请在服务器启动时设置Performance_schema_users_size 系统变量。 要禁用用户统计信息,请将此变量设置为 0。[1]

假设我们有个以“天”为单位粒度的需求,要求记录每个用户上次登录数据库是哪一天。

实现

对于该需求,我们可以创建一张新表 myuser,该表记录用户名 user,总连接次数 total_conn,和更新时间 update_at。

create table myuser(
user char(32) primary key,
total_conn bigint,
update_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

创建一个事件 myevent,每天零时从 performance_schema.users 抽取数据写入到 myuser 表。

CREATE EVENT myuser_daily
ON SCHEDULE EVERY DAY
DO
xxx

VALUES()

这里,将用到 INSERT ... ON DUPLICATE KEY UPDATE

NSERT … ON DUPLICATE KEY UPDATE 是 MySQL 中的一个非常有用的特性,该语句允许你插入一行数据,但如果遇到重复的主键或唯一键约束,则更新已存在的记录而不是插入新记录。这个特性在需要做"upsert"(即更新或插入)操作时特别有用。

  • 语法

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE column1 = value1, column2 = value2, ...;

  • 使用方法
  1. 确保表有唯一索引或主键:ON DUPLICATE KEY UPDATE 依赖于唯一索引或主键来确定是否发生冲突。
  2. 构建 INSERT 语句:列出你想要插入的列和值。
  3. 添加 ON DUPLICATE KEY UPDATE 子句:在子句中,指定当发生冲突时你想要更新的列和新的值。
  • 案例

假设我们有一个名为 products 的表,其中包含 id(产品ID)、name(产品名称)和 price(产品价格)列。id 列是唯一的。

CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(50),
price DECIMAL(10,2)
);

现在,我们想插入一个新的产品或更新一个已存在的产品。我们尝试插入一个具有指定 id 的产品,如果该 id 已存在,则更新 name 和 price。

INSERT INTO products (id, name, price)
VALUES (1, 'Laptop', 999.99)
ON DUPLICATE KEY UPDATE name = VALUES(name), price = VALUES(price);

在这个例子中,如果 id 为 1 的产品已经存在,那么 name 和 price 将被新的值更新。如果没有冲突,将插入一个新记录。

  • 注意事项
  1. ON DUPLICATE KEY UPDATE 子句中使用的 VALUES() 函数返回的是 INSERT 语句中尝试插入的值。
  2. 这个特性可以与多列值插入一起使用,但要注意,如果多列值中的任何一个违反了唯一性约束,整个操作将转变为更新操作。
  3. 在使用 ON DUPLICATE KEY UPDATE 时,确保你的表结构和索引设计能够有效地处理潜在的冲突。

我们可以使用 VALUES() 方法来更新数据:

INSERT INTO myuser (user,total_conn) VALUES ('shawnyan',1)
ON DUPLICATE KEY UPDATE total_conn = values(total_conn);

这里的第二个 VALUES() 是一个特殊的函数,它返回 INSERT 语句中对应的列的值。这允许你引用你试图插入的值,而不是表中的现有值。

但从 MySQL 8.0.20 开始,该函数的用法被废弃,并在未来的版本中移除。继续使用会抛出如下告警:

Warning (Code 1287): 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead

建议使用别名来代替。[2]

  • 具体用法:

insert myuser(user,total_conn)
select USER,TOTAL_CONNECTIONS
from performance_schema.users AS new
where new.user is not null
ON DUPLICATE KEY UPDATE total_conn = new.TOTAL_CONNECTIONS;

  • 输出:

(root@localhost) [yandb]> select * from myuser;
+-----------------+------------+---------------------+
| user | total_conn | update_at |
+-----------------+------------+---------------------+
| event_scheduler | 1 | 2024-03-21 14:49:30 |
| root | 7 | 2024-03-21 14:54:57 |
| shawnyan | 1 | 2024-03-21 14:36:57 |
+-----------------+------------+---------------------+
3 rows in set (0.00 sec)

(root@localhost) [yandb]> r
Connection id: 17
Current database: yandb

(root@localhost) [yandb]> insert myuser(user,total_conn)
-> select USER,TOTAL_CONNECTIONS
-> from performance_schema.users AS new
-> where new.user is not null
-> ON DUPLICATE KEY UPDATE total_conn = new.TOTAL_CONNECTIONS;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

(root@localhost) [yandb]> select * from myuser;
+-----------------+------------+---------------------+
| user | total_conn | update_at |
+-----------------+------------+---------------------+
| event_scheduler | 1 | 2024-03-21 14:49:30 |
| root | 8 | 2024-03-21 14:55:12 |
| shawnyan | 1 | 2024-03-21 14:36:57 |
+-----------------+------------+---------------------+
3 rows in set (0.00 sec)

小结

MySQL 8 的各项特性一直在改进优化,在使用新版本或者升级时,也需要注意一下废弃的特性,及时更新应用程式。

– END. –

如果这篇文章为你带来了灵感或启发,就帮忙点『赞』or『在看』or『转发』吧,这对我非常重要,感谢!(๑˃̵ᴗ˂̵)

  1. https://dev.mysql.com/doc/refman/8.0/en/performance-schema-users-table.html ↩︎

  2. https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_values ↩︎

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论