13 May 2021

Mysql/Mariadb: Spalte ändern: VARCHAR zu DATE

Problem: Mann hat in einer Mysql- oder Mariadb-Datenbank eine Spalte, in der man Datums-Werte speichert, als VARCHAR(255) angelegt, und das zu spät gemerkt. Wie kann man sie auf den Type DATE konvertieren?

Dies ist ein nicht ganz so einfaches Unterfangen, man muss eine temporäre Spalte verwenden. Achtung - dieses Howto habe ich so ausprobiert, und bei mir hat das funktioniert. Auf jedenfall vorher einen Datenbank-Dump erstellen, es kann leicht was schiefgehen, wenn man sich nur minimal vertippt!

Zuerst eine neue Spalte mit dem Typ DATE anlegen
- und natürlich ansehen, ob das so plausibel funktioniert hat
- ich will hier die Spalte “carelevel_since” auf DATE konvertieren

ALTER TABLE ip_client_extended ADD cl DATE AFTER carelevel_since;
DESCRIBE ip_client_extended;
+-----------------------------+--------------+------+-----+
| client_id                   | int(11)      | YES  | UNI |
| carelevel                   | int(11)      | YES  |     |
| carelevel_since             | varchar(255) | YES  |     |
| cl                          | date         | YES  |     |
[...]

SELECT client_id, carelevel, carelevel_since, cl FROM ip_client_extended WHERE client_id=1;
+-----------+-----------+-----------------+------+
| client_id | carelevel | carelevel_since | cl   |
+-----------+-----------+-----------------+------+
|         1 |         0 | 2025-02-01      | NULL |
+-----------+-----------+-----------------+------+

Testen, ob Mysql STR_TO_DATE oder CAST AS DATE kann
Ich habe festgestellt, dass es bei mir nur mit CAST AS DATE schlussendlich korrekt funktioniert - YMMV

SELECT STR_TO_DATE('2017-12-01', '%Y-%m-%d') ;
SELECT STR_TO_DATE(carelevel_since, '%Y-%m-%d') FROM ip_client_extended;
SELECT CAST(carelevel_since AS DATE) FROM ip_client_extended;

Zuerst schauen wir uns nochmals die Ausgangslage an

SELECT client_id, carelevel, carelevel_since, cl FROM ip_client_extended WHERE carelevel_since;
+-----------+-----------+-----------------+------+
| client_id | carelevel | carelevel_since | cl   |
+-----------+-----------+-----------------+------+
|         3 |         2 | 2017-01-01      | NULL |
|         8 |         4 | 2017-01-01      | NULL |
|        19 |         3 | 2017-01-01      | NULL |
|        42 |         1 | 2019-05-01      | NULL |
|        54 |         3 | 2023-03-01      | NULL |
|        64 |         2 | 2022-11-01      | NULL |
[...]

Jetzt konvertieren wir das Datum in die Spalte cl

UPDATE ip_client_extended AS ice1 SET ice1.cl = (SELECT CAST(carelevel_since AS DATE) 
 FROM ip_client_extended AS ice2 WHERE  ice1.client_id = ice2.client_id);

Query OK, 160 rows affected, 0 warnings (0.045 sec)
Rows matched: 160  Changed: 160  Warnings: 0

Das Ergebnis kontrollieren

SELECT client_id, carelevel, carelevel_since, cl FROM ip_client_extended WHERE carelevel_since;
+-----------+-----------+-----------------+------------+
| client_id | carelevel | carelevel_since | cl         |
+-----------+-----------+-----------------+------------+
|         3 |         2 | 2017-01-01      | 2017-01-01 |
|         8 |         4 | 2017-01-01      | 2017-01-01 |
|        19 |         3 | 2017-01-01      | 2017-01-01 |
|        42 |         1 | 2019-05-01      | 2019-05-01 |
|        54 |         3 | 2023-03-01      | 2023-03-01 |
|        64 |         2 | 2022-11-01      | 2022-11-01 |

Schlussendlich die alte Spalte löschen und die neue umbenennen

ALTER TABLE ip_client_extended DROP COLUMN carelevel_since;
ALTER TABLE ip_client_extended RENAME COLUMN cl TO carelevel_since;