MYSQL charctor编码 [英] MYSQL charctor encoding
问题描述
在询问这个问题
Arth问我为什么每个字符集都有一个表，而不是一个表。现在为了简单，我知道链接表是一种方法，但我的当前平台不允许链接表。
在尝试展示为什么我创建不同表格的样品时，我使用了名词pants witch翻译成裤子パンツHosenБрюкиPantalonescalças取决于语言我创建了一个名为FIXME的新表，这里是表格。
CREATE TABLE`FIXME`（
` 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，
` 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;
现在为什么在使用单个表并应用信息时，一个简单的导出类似于
INSERT INTO`FIXME`（`ENName`，`ENDetails`，`ENDescriotion`，`ID`，`ItemID`，`Default`， `ZHName`，`ZHDetails`，`ZHDescriotion`，`DEName`，`DEDetails`，`DEDescriotion`，`PTName`，`PTDetails`，`PTDescriotion`，`RUName`，`RUDetails`，`RUDescriotion` ````JPDetails````````````````````````````````````````````````````````````````````）VALUES
（'Pants'，'一种服装，从腰部到踝部覆盖你的身体，每条腿的独立部分，一条裤子[=长裤]，1，-2147483648，1，¿¤¤l'，一件衣服，从腰部到踝部覆盖你的身体，每条腿的独立部分，一条裤子[=长裤]，一条衣服从腰部到脚踝覆盖你的身体，每条腿都有一个独立的部分一条裤子[=裤子]，一件衣服，从腰部到脚踝覆盖你的身体，每条腿都有一个独立的部分，一条裤子[=长裤] '¬ü¤u'ú¸ÕºX¼m8'，'一件衣服，从腰部到踝部覆盖你的身体，每条腿都有一个独立的部分'，'一条裤子[=长裤]'，'' ó'，'一件衣服，从腰部到脚踝覆盖你的身体，每条腿都有一个独立的部分'，'一条裤子[=长裤]'，'Pantalones'你的身体从腰部到踝部，并且每条腿都有一个单独的部分'，'一条裤子[=裤子]'），
（''，''，''，2，0， ''，''，''，''，''，''，''，''，''，''，'' ，''）;
所以这让我认为字符集在表列中不正确，检查每个人，他们是最好的MYSQL文档正确。现在，如果我们为每个语言创建一个表，然后将表设置为正确的编码，这很好。
那么如何更改单个表的字符集所有字符集的所有语言..所以单个查询可以更新的细节，默认和可选的描述。在一个表中的一切...或使用显然马虎和繁琐的每一种语言在自己的表方法更新每一行，以便一个人翻译为那里母语可以看到英语版本，并从那里调整的词????
cp866_general_ci 是一个排序规则，而不是字符集 。使用这样的：
CHARACTER SET cp866 COLLATION cp866_general_ci
但是...为什么不使用utf8mb4作为所有列？在这一点上，你可以有一个较窄的表（但有更多的行），具有该语言的名称，语言和翻译。
只要声明在执行 INSERTs 时编码 ，并且在执行 SELECTs 。这可以通过 SET NAMES ... 或等效的（例如PHP的 set_charset（））来完成。 p>
有了这个，你可能需要 JOINs 来重建多种语言，如果UI需要呈现它们。 >
使用InnoDB，而不是MyISAM。
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????
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.
这篇关于MYSQL charctor编码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
