When using MySQL, the load data infile is often used to import, and sometimes a format conversion problem is encountered when a column of type date is encountered:
First create a simple people table with name, birthday, age three fields:mysql> create table people (name varchar) not NULL, birthday date Not NULL, and age int is not NULL); Query OK, 0 rows affected (0.18 sec) constructs two test files, imports into the people table, such as: You can see two files only birthday format, we two files with load data local infile for import testing, The results are as follows:
You can see that the date in the Test1.txt format, such as%y-%m-%d, can be imported correctly, and the date in test2.txt, such as the%m/%d/%y format, cannot be imported correctly, prompting the data to be truncated. Check the following system variables related to date:
found that the default value of Date_format in MySQL is%y-%m-%d, trying to modify the value of the variable, return the following error:mysql> set date_format= '%m/%d/%y '; ERROR 1238 (HY000): Variable ' Date_format ' is a read only Variable then we set this value directly in the config file My.ini, restart the MySQL service, successfully modified the value of Date_format:
Re-import Test2.txt, this thought can be imported normally, the result is the same as the original, even the display format has not changed:mysql> select * FROM people;+------+------------+-----+| name | Birthday | Age |+------+------------+-----+| Lily | 0000-00-00 | 25 | | Lucy | 0000-00-00 | |+------+------------+-----+2 rows in Set (0.00 sec) Baidu It is said that this variable has been abandoned by MySQL ... So the question is, how do I properly import the date data in the non-default format? There are probably three ways to try it: 1. Use the editing software to convert the date format in the CSV file to the default MySQL date format, and then import it, 2. Use some third-party software to import, such as Navicat, with the date format conversion function; 3. On the load data local The infile statement uses the Str_to_date function for conversion: Personal recommendation using the third method, the actual test command line under the import data is much faster than through the client software import.
About date format issues when using load DATA infile to import CSV files in MySQL
