MYSQL charctor encoding

Question

While asking this question

Arth asked why I have one table for every charset rather then having one table with everything. Now for simplicity sake I know that linking tables is one way to do this but my current platform don't allow linking tables. So lets keep it simple.

While trying to show samples of why I created different tables I used the noun pants witch translates to 褲子 パンツ Hosen Брюки Pantalones calças depending on the language I crated a new table called FIXME here is the table..

CREATE TABLE `FIXME` (
  `ENName` varchar(50) CHARACTER SET utf8 NOT NULL,
  `ENDetails` text CHARACTER SET utf8 NOT NULL,
  `ENDescriotion` varchar(128) CHARACTER SET utf8 NOT NULL,
  `ID` int(255) NOT NULL,
  `ItemID` int(64) NOT NULL,
  `Default` tinyint(1) NOT NULL,
  `ZHName` varchar(50) CHARACTER SET big5_chinese_ci NOT NULL,
  `ZHDetails` text CHARACTER SET big5_chinese_ci NOT NULL,
  `ZHDescriotion` varchar(128) CHARACTER SET big5_chinese_ci NOT NULL,
  `DEName` varchar(50) CHARACTER SET latin1_german2_ci  NOT NULL,
  `DEDetails` text CHARACTER SET latin1_german2_ci  NOT NULL,
  `DEDescriotion` varchar(128) CHARACTER SET latin1_german2_ci NOT NULL,
  `PTName` varchar(50) CHARACTER SET latin1_bin NOT NULL,
  `PTDetails` text CHARACTER SET latin1_bin NOT NULL,
  `PTDescriotion` varchar(128) CHARACTER SET latin1_bin NOT NULL,
  `RUName` varchar(50) CHARACTER SET cp866_general_ci NOT NULL,
  `RUDetails` text CHARACTER SET cp866_general_ci NOT NULL,
  `RUDescriotion` varchar(128) CHARACTER SET cp866_general_ci NOT NULL,
  `JPName` varchar(50) CHARACTER SET sjis_japanese_ci NOT NULL,
  `JPDetails` text CHARACTER SET sjis_japanese_ci NOT NULL,
  `JPDescriotion` varchar(128) CHARACTER SET sjis_japanese_ci NOT NULL,
  `ESName` varchar(50) CHARACTER SET latin1_general_ci NOT NULL,
  `ESDetails` text CHARACTER SET latin1_general_ci NOT NULL,
  `ESDescriotion` varchar(128) CHARACTER SET latin1_general_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

ALTER TABLE `FIXME`
  ADD PRIMARY KEY (`ID`,`ItemID`) COMMENT 'ROW ID', ADD UNIQUE KEY `ID` (`ID`);

ALTER TABLE `FIXME`
  MODIFY `ID` int(255) NOT NULL AUTO_INCREMENT;

Now why when using a single table and applying the information a simple export looks like this

INSERT INTO `FIXME` (`ENName`, `ENDetails`, `ENDescriotion`, `ID`, `ItemID`, `Default`, `ZHName`, `ZHDetails`, `ZHDescriotion`, `DEName`, `DEDetails`, `DEDescriotion`, `PTName`, `PTDetails`, `PTDescriotion`, `RUName`, `RUDetails`, `RUDescriotion`, `JPName`, `JPDetails`, `JPDescriotion`, `ESName`, `ESDetails`, `ESDescriotion`) VALUES
('Pants', 'a piece of clothing that covers your body from the waist to the ankle and has a separate part for each leg', 'a pair of pants [=trousers]', 1, -2147483648, 1, '¿Ç¤l', 'a piece of clothing that covers your body from the waist to the ankle and has a separate part for each leg', 'a pair of pants [=trousers]', 'St踀e', 'a piece of clothing that covers your body from the waist to the ankle and has a separate part for each leg', 'a pair of pants [=trousers]', 'calÕºXas ', 'a piece of clothing that covers your body from the waist to the ankle and has a separate part for each leg', 'a pair of pants [=trousers]', '¬ü¤u´ú¸ÕºX¼m8', 'a piece of clothing that covers your body from the waist to the ankle and has a separate part for each leg', 'a pair of pants [=trousers]', '¼o±ó', 'a piece of clothing that covers your body from the waist to the ankle and has a separate part for each leg', 'a pair of pants [=trousers]', 'Pantalones ', 'a piece of clothing that covers your body from the waist to the ankle and has a separate part for each leg', 'a pair of pants [=trousers]'),
('', '', '', 2, 0, 0, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '');

So this Leeds me to think that the charsets are incorrect in the table columns but I assure you I have checked everyone and they are to the best of the MYSQL documentation correct. Now if we create a table for each Language then set the table to the proper encoding this works just fine.

So how do I change the charset of a single table to include all charsets for all languages.. So that a single query can update the details, default and optionally the description. With everything in one table... or using the apparently sloppy and cumbersome every language in its own table method update each row so that a person translating to there native language can see the English version and adjust the words from there????

Answer 1

cp866_general_ci is a "collation", not a "character set". Use something like this:

CHARACTER SET cp866 COLLATION cp866_general_ci

But... Why not use utf8mb4 for all columns? At that point, you could have a narrower table (but with more rows) that has the name, language, and translation in that language.

Just be sure to declare what encoding the client has when doing INSERTs, and what it wants to see when doing SELECTs. This can be done with SET NAMES ... or the equivalent (such as PHP's set_charset()).

With that, you might need JOINs to reconstruct the multiple languages if the UI needs to present them all.

Use InnoDB, not MyISAM.