如何將出生日期欄位的錯誤數據類型從 VARCHAR 更改為 DATE
我在使用生日欄位創建使用者表時犯了一個錯誤，我沒有放置 DATE 數據類型，而是放置了一個 VARCHAR！
所以現在我的使用者表看起來像這樣：
CREATE TABLE IF NOT EXISTS users ( id INT UNSIGNED NOT NULL, birthdate VARCHAR (200) NOT NULL, PRIMARY KEY (id) ) DEFAULT CHARSET=utf8;它填充如下（範例）：
INSERT INTO users (id, birthdate) VALUES (1,'1991-01-23'), (2,'yyyy-01-23'), (3,'1991-mm-23'), (4,'1991-01-dd'), (5,''), (6,'1991-01-d3'), (7,'1983-05-23'), (8,'1991-0m-23'), (9,'19yy-01-23'), (10,'y991-01-23');現在我想將每個不正確的生日更新為 NULL，或者設置一個預設值，例如 2020-01-01。在這裡查看我的 sqlfiddle 。
我為此使用了 dbfiddle.uk（請參見此處），而不是 sqlfiddle.com - 更多的伺服器，並且更新得更好。
所以，我所做的是以下（設置是根據你的sqlfiddle）：
CREATE TABLE IF NOT EXISTS users ( id int(6) unsigned NOT NULL, birthdate varchar(200) NOT NULL, PRIMARY KEY (id) )DEFAULT CHARSET=utf8;填充：
INSERT INTO users (id, birthdate) VALUES (1,'1991-01-23'), (2,'yyyy-01-23'), (3,'1991-mm-23'), (4,'1991-01-dd'), (5,''), (6,'1991-01-d3'), (7,'1983-05-23'), (8,'1991-0m-23'), (9,'19yy-01-23'), (10,'y991-01-23');添加一列以保存有效值：
ALTER TABLE users ADD new_bdate DATE; -- add column to hold valid values請注意，新欄位可以為空。它必須是，除非你想輸入一些預設值，比如 01/01/1900 或 ‘0000-00-00’ 或 ‘2020-01-01’ - 我會（強烈）建議不要這樣做！它在計算 PLAN 時會混淆優化器，並且
NULL在您的數據未知時完全有效！您在評論中提到嘗試使用 ‘0000-00-00’ 作為預設值失敗。這是因為
sql_mode包含STRICT_TRANS_TABLES- 預設情況下在 MySQL 5.7 中啟用（請參閱此處的文件） - 在此處和此處進一步討論（以及隨附的連結和評論）。來自 MySQL 文件：嚴格模式影響伺服器是否允許“0000-00-00”作為有效日期：如果未啟用嚴格模式，則允許“0000-00-00”並且插入不會產生警告。如果啟用了嚴格模式，則不允許使用“0000-00-00”並且插入會產生錯誤，除非也給出了 IGNORE。對於 INSERT IGNORE 和 UPDATE IGNORE，允許使用“0000-00-00”，並且插入會產生警告。
因此，現在我們將
birthdate欄位更改為接受NULLs - 這在以後“清理”完成時很重要：ALTER TABLE users MODIFY birthdate VARCHAR (200) NULL; -- make birthdate nullable -- this is important for the STR_TO_DATE function.我還使原始
birthdate欄位可以為空。如果這沒有完成，那麼第一個UPDATE在TRANSACTION下面失敗：START TRANSACTION; UPDATE users SET birthdate = NULL WHERE birthdate REGEXP '[a-zA-Z/]' OR birthdate = ''; UPDATE users SET new_bdate = birthdate WHERE birthdate IS NOT NULL; COMMIT;在一個事務中執行兩個 DML 步驟/查詢以避免步驟之間的任何更新是很重要的——儘管我想你可以在安靜的時間這樣做——或者你可以在更改期間鎖定表。
- 正則表達式的解釋：
$$ a-z $$表示匹配範圍內的所有字元$$ a, b, c… x, y, z $$,
A-Z大寫字母的含義相同。該/字元將匹配斜線 - 可用於日期，但對 MySQL 日期無效。現在，我們清理；
ALTER TABLE users DROP COLUMN new_bdate;最後，我們檢查我們的結果：
SELECT * FROM users;結果：
id birthdate 1 1991-01-23 2 NULL 3 NULL 4 NULL 5 NULL 6 NULL 7 1983-05-23 8 NULL 9 NULL 10 NULL 10 rows所以，現在我們有一個具有正確數據類型和正確值的列（這些都是已知的）。
您的問題說明了不允許在應用程序中輸入自由文本的普遍重要性。如果可能的話，使用者應該有義務從下拉列表中進行選擇，並從一開始就確保 NOT NULL 約束！
此外，它還顯示了從第一天開始選擇正確數據類型的重要性！您的數據庫是您保護數據的最後堡壘，因此請確保輸入的任何內容從一開始就有效- 您將避免這樣的問題！
編輯：
VARCHAR在 OP 發表評論之後，特別是關於在欄位中輸入的日期YYYY/mm/dd（即分隔符/而不是 `-`` - @Akina 的（優雅）REGEXP答案可以修改如下（在添加合適的日期後 - 請參閱此處的小提琴） .INSERT INTO `users` (`id`, `birthdate`) VALUES (11, '1993/03/20'), (12, '2000/09/25'); (13, '2015.06.30'), (14, '2015_04_15');請注意使用斜線 (
/)、點 (.) 或下劃線 (_) 字元作為不同日期子欄位的分隔符。SQL：
UPDATE users SET DOB = STR_TO_DATE ( CONCAT ( SUBSTRING(birthdate, 1, 4), '-', SUBSTRING(birthdate, 6, 2), '-', SUBSTRING(birthdate, 9, 2) ), '%Y-%m-%d' ) WHERE birthdate REGEXP '[0-9]{4}.[0-9]{2}.[0-9]{2}';結果（為簡潔起見）：
id birthdate DOB ... ... 10 y991-01-23 NULL 11 1993/03/20 1993-03-20 12 2000/09/25 2000-09-25 13 2015.06.30 2015-06-30 14 2015_04_15 2015-04-15 14 rows這是從@Akina 的答案略微修改的 - 它在正則表達式中使用點 (’.’) - 點是正則表達式元字元（或“特殊”字元），它是可以代表任何單個 (即一個且只有一個）字元。
因此，任何年份後跟任何單個字元，任何月份，任何單個字元，任何一天都將匹配 - 這將涵蓋有效的 ISO 日期（使用連字元 (
-) 或其他可能的分隔符，即下劃線或文字點字元。日期正則表達式有些簡化——日期的實數可能要復雜得多！我從字元串中提取日期非常棘手。我使用了 MySQL（非標準 - quelle 驚喜！）字元串連接運算符（加號（
+））符號，它開始添加（即以數字方式）年份編號和月份編號。MySQL 版本的（標準 SQL）雙管道 (||) 運算符也發生了同樣的事情。只有當我發現這一點時，我才設法得到最終的工作CONCAT解決方案！正如我的最後一個連結所說，
"ya gotta love MySQL“ - 這不是我第一個情緒……我在這個論壇上為 PostgreSQL 宣傳的另一個原因！要將您的生日欄位設置為預設值 2020-01-01（不建議…見上文），請使用 @Akina評論中的程式碼：
SET DOB = CASE WHEN birthdate REGEXP {pattern 1} THEN {expression 1} WHEN {pattern 2} THEN {expression 2} ... ELSE NULL END像這樣：
UPDATE users SET DOB = CASE WHEN birthdate REGEXP '[0-9]{4}.[0-9]{2}.[0-9]{2}' THEN STR_TO_DATE ( CONCAT ( SUBSTRING(birthdate, 1, 4), '-', SUBSTRING(birthdate, 6, 2), '-', SUBSTRING(birthdate, 9, 2) ), '%Y-%m-%d' ) ELSE '2020-01-01' END;看這裡的小提琴。+1 一個有趣的第一個問題，看起來（看似）簡單但讓我思考 - 歡迎來到論壇！