如何让mysql忽略名称中的特殊字符

Question

I am trying to do a CSV bulk import of names into MYSQL, but during the process there is a special characters that halts the operation.

The character over the name - Pérez

Is there a way to have MYSQL to ignore this on upload? My next step is to automate the upload via a web page where a customer can just upload the CSV file and hit submit, therefore hoping to work out these glitches.

I took the suggestion of the panel and recreated my table as UTF8-Default.

ERROR 1366: Incorrect string value: '\xE9rez' for column 'acct_owner' at row 1 SQL Statement:

I tried this and I still get the same error on that special character, plus now for some reason my auto-increment column does not increment, it just captures the data from the last_update column, therefore everything shifts left.

Answer 1

Well in case you want to insert/load csv data with special character you can try like this

LOAD DATA INFILE 'file.csv'
IGNORE INTO TABLE table
CHARACTER SET UTF8
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '
'