Secondly, I am using mysql 5.7 Linux operating system, has produced the following error when importing data
ERROR 1300 (HY000) : Invalid utf8 character string: "'
On the BBS n the search for a long time, almost all the code is modified to utf8, but useless, also tried to change the CSV file to TXT file, also was not used, finally found this post, according to the steps, found finally guide table successful,
Specific operation process:
When the load CSV into mysql and submit them to the wrong, distress for a long time, the Internet search no answers,
Mysql> The load data infile '/home/HDH/8 _sr/8 _45. CSV' into table xizang. Fp45 fields terminated by ', 'escaped by' "' lines terminated by '\ r \ n';
Solution:
1. Check the character set, Linux, database, table
Mysql> Show variables like '%' character. +--------------------------+-----------------------------+| Variable_name | Value |+--------------------------+-----------------------------+| character_set_client | utf8 || character_set_connection | utf8 || character_set_database | utf8 || character_set_filesystem | binary || character_set_results | utf8 || character_set_server | utf8 || character_set_system | utf8 || character_sets_dir |/home/mysql/share/charsets/|+--------------------------+-----------------------------+8 rows in set (0.01 sec)
Are utf8, no problem,
2. Only in this file for special characters cleared, commonly encountered this all because of characters among the escape character '\',
Commonly encountered when special characters will be prompted to how many rows, what characters lead to error, but this "", there won't be so detailed tips,
Sed to the document -i 's/"//g' filename # delete files in the
"
Sed -i 's/\ \//g' filename # delete files in the \
The load again ok
CodePudding user response:
At the back of the solution did not understand,,1. The specific command is # in front of the two words?
2. You have special characters in file deleted, how to use the files? Because the original file, there may be a date data type, just use \ symbols, such as,
CodePudding user response:
Yes, the first is to delete files in the"Specific command to sed -i 's/"//g' filename
The second is to remove the file \
Specific command to sed -i 's/\ \//g' filename