mysql - mysql 中的日期格式%d/%m%y

Question

为了简单起见，我将缩短问题:

我想导入包含以下数据的文件:

21/01/1896
03/04/1905
01/05/1906
04/08/1905
28/07/1904
01/11/1908
04/08/1904
03/01/1915
03/11/1903
27/11/1887
25/03/1903
25/05/1901
24/12/1889
01/01/1910

mysql> DESCRIBE Fechas;
+--------+------+------+-----+---------+-------+
| Field  | Type | Null | Key | Default | Extra |
+--------+------+------+-----+---------+-------+
| Fechas | date | YES  |     | NULL    |       |
+--------+------+------+-----+---------+-------+

所以我尝试导入文件:

LOAD DATA LOCAL INFILE 'Fechas' INTO TABLE Fechas;

导入后:

mysql> select * from Fechas limit 5;
+------------+
| Fechas     |
+------------+
| 0000-00-00 |
| 0000-00-00 |
| 0000-00-00 |
| 0000-00-00 |
| 0000-00-00 |
+------------+

如果看一下变量:

mysql> show variables like '%time%';
+----------------------------+-------------------+
| Variable_name              | Value             |
+----------------------------+-------------------+
| connect_timeout            | 10                |
| datetime_format            | %d/%m/%Y %H:%i:%s |
| delayed_insert_timeout     | 300               |
| flush_time                 | 0                 |
| innodb_lock_wait_timeout   | 50                |
| innodb_rollback_on_timeout | OFF               |
| interactive_timeout        | 28800             |
| lc_time_names              | es_AR             |
| long_query_time            | 10.000000         |
| net_read_timeout           | 30                |
| net_write_timeout          | 60                |
| slave_net_timeout          | 3600              |
| slow_launch_time           | 2                 |
| system_time_zone           | ART               |
| table_lock_wait_timeout    | 50                |
| time_format                | %H:%i:%s          |
| time_zone                  | SYSTEM            |
| timed_mutexes              | OFF               |
| timestamp                  | 1395284423        |
| wait_timeout               | 28800             |
+----------------------------+-------------------+

当我执行选择时，它输出:

mysql> select * from Fechas limit 5;
+------------+
| Fechas     |
+------------+
| 0000-00-00 |
| 0000-00-00 |
| 0000-00-00 |
| 0000-00-00 |
| 0000-00-00 |
+------------+

一组 5 行(0.00 秒)

我做错了什么吗？为什么即使设置 datetime_format 它仍然以格式 %Y-%m-%d 显示？我该怎么做才能 mysql 将字段显示为源文件中的日期格式？

提前致谢!

Answer 1

仅当仅从选择查询显示时才能更改格式。

SELECT DATE_FORMAT(Fechas,'%d/%m/%Y') FROM Fechas

您只能以默认格式存储日期/时间。至于 datetime_format 变量。它未使用。请参阅here