Cannot import French and special characters
When I try to import a dump file into MySQL, for some reason none of the special characters are imported.
I have a bilingual Web site that has options in two languages, one being French, the special characters are not imported.
For instance:
INSERT INTO `options` (`id`, `type_id`, `sort_order`, `english`, `french`) VALUES (33, 5, 999, 'Dark grey', 'Gris foncé');
"Gris foncé" becomes "Gris fonc".
My local (target) version is MySQL 4.1.13a on XP Pro. My server (source) version is a 3.23 on a LInux server. When I export I make sure to export as a 3.23 version, of course.
In the ascii file, the "é" is the regular alt+0233 character.
The table "options" is created as follow:
DROP TABLE IF EXISTS `options`;
CREATE TABLE `options` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`type_id` tinyint(3) unsigned NOT NULL default '0',
`sort_order` smallint(5) unsigned NOT NULL default '999',
`english` varchar(100) NOT NULL default '',
`french` varchar(100) default NULL,
KEY `id` (`id`)
) TYPE=MyISAM;
I have tried all sorts of collations: utf8-unicode-ci, utf8-general-ci, utf8-roman-ci, all the latin1 collations, to no avail: the "é" and all the other special characters are left behind!
The same thing happens when I do an export from my local computer to the server, even with plain English characters: special characters such as the "m" dash are simply replaced by garbage (granted, I should have HTML encoded, but the issue is not here).
What is the best way to make sure that I do not lose any characters when exporting and importing dumps between servers?
And how can I import my dump locally so that "Gris foncé" remains "Gris foncé" and not "Gris fonc?"