List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:January 18 2008 7:32pm
Subject:Re: Handling Special Characters
View as plain text  
Jerry,

On Jan 18, 2008 2:27 PM, Jerry Schwartz <jschwartz@stripped> wrote:
> I am having trouble inserting special characters into a table. I am using
> the MySQL client. I put the following commands into a text file (I'm on
> WinXP, using Notepad), copy them, and paste them into the MySQL command line
> client.
>
> SET NAMES utf8;
>
> CREATE TEMPORARY TABLE `giiexpr_db`.`eo_name_table` (
> `eo_name` VARCHAR( 255 ) NOT NULL
> ) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
>
> INSERT INTO eo_name_table
>     (eo_name)
> VALUES
> ("Associated British Foods Plc (Abf) - Hot Drinks - World");
>
> SELECT * FROM eo_name_table;
> +---------------------------------------------------------+
> | eo_name                                                 |
> +---------------------------------------------------------+
> | Associated British Foods Plc (Abf) - Hot Drinks - World |
> +---------------------------------------------------------+
> 1 row in set (0.04 sec)
>
> That symbol before "World" is an N-dash, 0x96. This works perfectly.
>
> Here's where things go wrong. If instead of pasting these commands into the
> client, I source the exact same file, I get this:
>
> +-------------------------------------------------+
> | eo_name                                         |
> +-------------------------------------------------+
> | Associated British Foods Plc (Abf) - Hot Drinks |
> +-------------------------------------------------+
> 1 row in set (0.04 sec)
>
> It seems that special characters are treated differently when they are in a
> sourced file. It isn't just the N-dash, French accented characters cause the
> same problem. These are all single-byte characters.
>
> Just to be sure, I checked the length of the string in the field, and it
> accurately reflected the difference. That proves, to my satisfaction, that
> the problem is on the input side.
>
> I'm using version 4.1.22 community server on a Linux platform. I'm using
> version 5.0.45 of the CLI client.
>
> Anyone have any ideas? I was trying to avoid having to write a program to do
> this.

After you get the data you want into the table via copy/paste, can you
dump and reload it correctly with mysqldump > dump.sql and mysql <
dump.sql?  If so, try peeking into the dump file and see how it's
written there.
Thread
Handling Special CharactersJerry Schwartz18 Jan
  • Re: Handling Special CharactersBaron Schwartz18 Jan
    • RE: Handling Special CharactersJerry Schwartz19 Jan
    • RE: Handling Special CharactersJerry Schwartz21 Jan
  • Re: Handling Special CharactersSebastian Mendel21 Jan
    • RE: Handling Special CharactersJerry Schwartz21 Jan