需要更改存储在 VARCHAR 列中的时间值

假设如下表结构和表名：

MariaDB [freeradius]> SHOW CREATE TABLE users\G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(64) DEFAULT NULL,
  `attribute` varchar(64) DEFAULT NULL,
  `op` char(2) DEFAULT NULL,
  `value` varchar(253) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2001 DEFAULT CHARSET=latin1

并拥有此示例数据：

MariaDB [freeradius]> SELECT * FROM users;
+------+----------+--------------------+------+----------------------+
| id   | username | attribute          | op   | value                |
+------+----------+--------------------+------+----------------------+
| 1980 | 11test   | Cleartext-Password | :=   | 12345                |
| 1981 | 11test   | Expiration         | :=   | 29 Jul 2018 12:00:00 |
| 1982 | 11test   | Simultaneous-Use   | :=   | 1                    |
| 2000 | 22test   | Expiration         | :=   | 30 Jul 2018 12:00:00 |
+------+----------+--------------------+------+----------------------+
4 rows in set (0.000 sec)

假设所有要更改的数据都具有“过期”属性，并且都具有 12:00:00 时间戳，

以下UPDATE命令应该有效：

MariaDB [freeradius]> UPDATE users SET value = DATE_FORMAT(STR_TO_DATE(value, '%e %b %Y %H:%i:%S') - INTERVAL 12 HOUR, '%e %b %Y %H:%i:%S') WHERE attribute = 'Expiration';           
Query OK, 2 rows affected (0.001 sec)
Rows matched: 2  Changed: 2  Warnings: 0

MariaDB [freeradius]> SELECT * FROM users;
+------+----------+--------------------+------+----------------------+
| id   | username | attribute          | op   | value                |
+------+----------+--------------------+------+----------------------+
| 1980 | 11test   | Cleartext-Password | :=   | 12345                |
| 1981 | 11test   | Expiration         | :=   | 29 Jul 2018 00:00:00 |
| 1982 | 11test   | Simultaneous-Use   | :=   | 1                    |
| 2000 | 22test   | Expiration         | :=   | 30 Jul 2018 00:00:00 |
+------+----------+--------------------+------+----------------------+
4 rows in set (0.001 sec)

您可以先使用只读查询对其进行测试：

SELECT DATE_FORMAT(STR_TO_DATE(value, '%e %b %Y %H:%i:%S') - INTERVAL 12 HOUR, '%e %b %Y %H:%i:%S')
FROM users
WHERE attribute = 'Expiration';

如果数据未规范化，或者取决于确切的日期格式，确切的更新操作可能会发生变化，并且不是 100% 清楚（例如，一个月中的 1 位数字是否以 0 开头？）。

这回答了你的问题，但我想在你的 schema 上发出一些危险信号，这太重要了，更不用说了：

• 没有理由在您的数据库中保留用户的明文密码。我不需要证明这一点，已经有很多文献可以为我做到这一点：http: //plaintextoffenders.com/faq/devs请对这些字符串进行哈希处理和加盐，或者让您的供应商这样做
• 您提出的问题是Entity-Attribute-Value 在某些/许多情况下不是一个好主意的原因之一：请参阅https://softwareengineering.stackexchange.com/questions/93124/eav-is-it-really-bad-正如第一个答案所暗示的那样，在所有场景中以及它如何导致自我悬挂。更多相关信息：https ://www.slideshare.net/billkarwin/sql-antipatterns-strike-back/16-EntityAttributeValue_If_you_try_and

如果我没有错过任何东西，一个简单的replace()应该做。

UPDATE elbat
       SET value = replace(value, '12:00:00', '00:00:00')
       WHERE attribute = 'Expiration';