日期格式%d/%m%y在mysql。

I will enshortened the proble for the sake of simplicity:

为了简单起见，我将把序言缩短:

I'd want to import a file with the following data:

我想用以下数据导入一个文件:

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    |       |
+--------+------+------+-----+---------+-------+

So I try to import the file:

所以我尝试导入文件:

LOAD DATA LOCAL INFILE 'Fechas' INTO TABLE Fechas;

After import:

导入后:

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

If a take a look at variables:

如果我们看一下变量:

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             |
+----------------------------+-------------------+

When I perform a select it outputs:

当我执行一个select it输出:

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

5 rows in set (0.00 sec)

5行(0.00秒)

Am I doing something wrong? Why even setting the datetime_format it persists on showing in format %Y-%m-%d ? What have I to do to mysql shows the fields as the date format from the source file?

我做错什么了吗?为什么还要设置datetime_format来显示格式%Y-%m-%d ?我要对mysql做什么呢?从源文件中显示字段作为日期格式?

Thanks in advance!

提前谢谢!

1 个解决方案

#1

You can only change the format when you are displaying from the select query only.

只有在从select查询中显示时，才能更改格式。

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

You can only store the date/time in the default format. As for the datetime_format variable. It is unused. See here

您只能将日期/时间存储在默认格式中。至于datetime_format变量。它是闲置的。在这里看到的

#1