John Nagle <nagle@animats.com> wrote:
>
> LOAD DATA LOCAL INFILE "c:\\docume~1\\nagle\\locals~1\\temp\\tmpi16zz2.tx t" REPLACE
> INTO TABLE companyindex CHARACTER SET utf8 FIELDS ENCLOSED BY '"' ESCAPED BY
> '\\' TERMINATED
> BY ',' (conformed_company_name, domain, location, state, postal_code,
> country_code, database_name, record_id)
>
>> _mysql_exceptions.ProgrammingError: (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL se
>> rver version for the right syntax to use near \'CHARACTER SET utf8 FIELDS ENCLOSED BY \'"\' ESCAPED BY \'\\\\\' TERMINATED BY \',\'
>> (con\' at line 1')
>
> If I remove the "CHARACTER SET utf8", SQL accepts it, FIELDS
> and all, and loads ASCII data correctly.

> That should be the right syntax, per
> http://dev.mysql.com/doc/refman/5.0/en/load-data.html
>
> Here's my MySQL installation:
> MySQL version: 5.0.27.

Maybe this was changed recently, but the manual page you reference
says:

"Beginning with MySQL 5.0.38, if the contents of the input file use
a character set that differs from the default, it is possible (and
usually preferable) to use the CHARACTER SET clause to specify the
character set of the file."

So your MySQL is too old for that feature.


> Given that, what's the correct way to tell MySQL that it
> is reading a UTF8 file?

From the same manual page:

"The character set indicated by the character_set_database system
variable is used to interpret the information in the file. SET NAMES
and the setting of character_set_client do not affect interpretation
of input."



XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/