The younger brother new MySQL, encounter problems to check for a long time also didn't find out problems, various genuflect is begged god help!
Problem: I will be the CSV file using the load data function (already converted to utf8 - CSV format) into my new table mytable, then select * from mytable can query to all the lines, but according to the field the query select * from mytable where col. The name='XXX' when there is a problem: if the col. The data in the name field is numeric or text type, the query returns the eligible records; If col. The data in the name field is English string, there is no record returned, although list
There is conforms to the query conditions,
Reason: the younger brother online for a long time and a lot of similar problems are not carried out in accordance with the field data for Chinese query, but I was not carried out in accordance with the field data for English query, really have a headache, and whether the CSV file's data encoding format is not fully converted to utf8 format, leading to the new table corresponding field do not agree with the data encoding format?

With the show variables like 'character_set_database';
Looked at the mysql database and table encoding formats are utf8 format, don't know where the problem is, please master answer, thank you!!!!!

Variable_name Value | |
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - | -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - |
Character_set_client | utf8 |
Character_set_connection | utf8 |
Character_set_database | utf8 |
Character_set_filesystem | binary |
Character_set_results | |
Character_set_server | utf8 |
Character_set_system | utf8 |
Character_sets_dir | C: \ Program Files \ MySQL \ MySQL Server 5.7 \ share \ charsets \ |

CodePudding user response:

What is check the field type, field space saved

CodePudding user response:

reference 1st floor yzg001212 response:

check what is the field type, field have saved space

Thank you reply my condition field is varchar (255) of the definition of the column in the data are in English I use wildcard string checked also is not the problem of space is not according to the field of the data are in English string query and data types are numeric or Chinese characters can be

CodePudding user response:

reference 1st floor yzg001212 response:

check what is the field type, field have saved space

Eldest brother, I just tried the wildcard, found that is truly a space problem,
But I don't understand the CSV after reading into the new table data behind why the blank space in English? Is the original CSV have space or read in the process of the space in the back of the new data in the table?

CodePudding user response:

Arguably VARCHAR field type of save data when the database will not automatically fill the blank space, so is still in the storage data itself contains Spaces,
May be your CSV file is in the process of encoding conversion problem, a simple test, after is to load the file directly imported into the new table (regardless of the Chinese characters), and then see if pure English field kept normal,