在之前的文章 MySQL 管理运维：妙用 DATETIME 数据类型的默认值 中，介绍了如何巧用自动更新字段时间戳。
本文将继续扩展该案例，使用
INSERT ... ON DUPLICATE KEY UPDATE 语句来检查记录用户登录情况。
背景
用户登录数据库的时间、次数，执行的语句，都是重要的审计项目。在老版本的 MySQL 中没有记录用户密码最后更改时间和用户最后登录时间，需要依靠外挂工具实现。
在 MySQL 8 中，系统表
mysql.user 的
password_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，该语句允许你插入一行数据，但如果遇到重复的主键或唯 一键约束，它会更新现有的行而不是抛出错误。
我们可以使用
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 | 2023-05-21 14:49:30 | | root | 7 | 2023-05-21 14:54:57 | | shawnyan | 1 | 2023-05-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 | 2023-05-21 14:49:30 | | root | 8 | 2023-05-21 14:55:12 | | shawnyan | 1 | 2023-05-21 14:36:57 | +-----------------+------------+---------------------+ 3 rows in set (0.00 sec)
小结
MySQL 8 的各项特性一直在改进优化，在使用新版本或者升级时，也需要注意一下废弃的特性，及时更新应用程式。
