問題描述
我必須將一些包含未編碼為 Unicode 的國(guó)家字符的數(shù)據(jù)導(dǎo)入到獨(dú)立的 MySQL 版本:5.0.18 在 Windows7 64 位上運(yùn)行.經(jīng)過一些最初的問題，我終于讓它在 MySQL 控制臺(tái)中工作.
I have to import some data containing national characters not coded as Unicode into stand alone MySQL Version: 5.0.18 running on Windows7 64bit. After some initial problems I finally got it working in MySQL console.
但由于數(shù)據(jù)超過 50 MB，因此無法在控制臺(tái)中輸入和/或使用剪貼板.所以我創(chuàng)建腳本文件才發(fā)現(xiàn)導(dǎo)入后國(guó)字是亂碼.
But as the data is more than 50 MByte typing into console and or using clipboard is not possible. So I created script file only to find out that the national characters are gibberish after import.
問題是，如果我對(duì)任何文件使用 source 命令，編碼就會(huì)中斷.如果我打開同一個(gè)文件并通過剪貼板將內(nèi)容復(fù)制到控制臺(tái)，則一切正常.這里最小的 MCVE 用于測(cè)試:
The problem is that if I use source command with any file the encoding broke. If I open the same file and copy the stuff to console by clipboard all works as should. Here smallest MCVE to test this:
DROP DATABASE IF EXISTS dbs;
CREATE DATABASE dbs;
USE dbs;
SET NAMES latin2;
DROP TABLE IF EXISTS `tab`;
CREATE TABLE `tab` (`ix` INT default 0,`nam` VARCHAR(50) default '' );
INSERT INTO `tab` VALUES
(1,'aacdeillnoorrstuuyzAACDEILLNOORRSTUUYZ'),
(2,'á???éí??ňó?????ú y?á ??éí???ó ????ú Y?');
SELECT * FROM `tab`;
當(dāng)我通過剪貼板將其復(fù)制到MySQL控制臺(tái)時(shí)，輸出如下:
When I copy this to MySQL console by clipboard the output is like this:
+------+----------------------------------------+
| ix | nam |
+------+----------------------------------------+
| 1 | aacdeillnoorrstuuyzAACDEILLNOORRSTUUYZ |
| 2 | á???éí??ňó?????ú y?á ??éí???ó ????ú Y? |
+------+----------------------------------------+
2 rows in set (0.00 sec)
這是需要的.但是當(dāng)我將所有這些放入 test.sql 文件并運(yùn)行
Which is desired. But when I put all this into test.sql file and run
source test.sql;
我得到了這個(gè)輸出:
+------+----------------------------------------+
| ix | nam |
+------+----------------------------------------+
| 1 | aacdeillnoorrstuuyzAACDEILLNOORRSTUUYZ |
| 2 | ?ń?′úYň??ˇ?ó°ü?˙ ?×┴ ╚¤╔═┼╝?? └ě??┌ ?? |
+------+----------------------------------------+
這顯然是錯(cuò)誤的(看起來像一些默認(rèn)的 MS-DOS 字符集).我認(rèn)為問題不在于表或數(shù)據(jù)庫(kù)，因?yàn)檫@對(duì)于純文本輸出來說是一樣的:
Which is obviously wrong (looks like some default MS-DOS charset). I think the problem is not on side of table nor database as this is the same for pure text outs like:
SET NAMES latin2;
SELECT 'á???éí??ňó?????ú y?á ??éí???ó ????ú Y?' AS 'aacdeillnoorrstuuyzAACDEILLNOORRSTUUYZ';
帶有剪貼板的輸出:
+----------------------------------------+
| aacdeillnoorrstuuyzAACDEILLNOORRSTUUYZ |
+----------------------------------------+
| á???éí??ňó?????ú y?á ??éí???ó ????ú Y? |
+----------------------------------------+
和 source 文件:
+----------------------------------------+
| aacdeillnoorrstuuyzAACDEILLNOORRSTUUYZ |
+----------------------------------------+
| ?ń?′úYň??ˇ?ó°ü?˙ ?×┴ ╚¤╔═┼╝?? └ě??┌ ?? |
+----------------------------------------+
就像從文件導(dǎo)入時(shí)編碼被搞砸了一樣.或者在通過鍵盤或剪貼板輸入到MySQL控制臺(tái)時(shí)，編碼發(fā)生了變化.
It is like when importing from files the encoding got screwed. Or while inputing to MySQL console through Keyboard or Clipboard the coding got changed.
那么發(fā)生了什么以及如何糾正這個(gè)問題(不丟失數(shù)據(jù))?
So what is going on and how to correct this (without the loss of data)?
- 使用
<命令行選項(xiàng)代替source沒有幫助 - 對(duì)
source使用-e命令行選項(xiàng)沒有幫助 - 使用默認(rèn)字符集命令行選項(xiàng)沒有幫助
- 對(duì)非 Unicode 字符串使用 UTF8 會(huì)導(dǎo)致數(shù)據(jù)過長(zhǎng)錯(cuò)誤和數(shù)據(jù)丟失
- 剪貼板中的數(shù)據(jù)與文件中的數(shù)據(jù)相同
- using
<command line option instead ofsourcedoes not help - using
-ecommand line option forsourcedoes not help - using default charset command line option does not help
- using UTF8 for non Unicode string lead to Data too long errors and loss of data
- data in clipboard is the same as in the file
好吧，我嘗試了 MySQL 5.7.19 的較新版本，這花了我很長(zhǎng)時(shí)間才開始，因?yàn)樗麄兏牧顺跏蓟蛢?nèi)容(wtf?那個(gè)瘋狂得到 1.8 GByte 沒有任何數(shù)據(jù)！).無論我做什么，它的行為都是一樣的.所以我嘗試使用 UTF8 編碼:
Well I tried newer version of MySQL 5.7.19 which took mi quite a while to get started as they change the initialization and things (wtf? that insanity got 1.8 GByte without any data !). It behaves the same no matter what I do. So I tried to use UTF8 encoding:
DROP DATABASE IF EXISTS dbs;
CREATE DATABASE dbs CHARACTER SET utf8 COLLATE 'utf8_unicode_ci';
USE dbs;
SET NAMES utf8;
DROP TABLE IF EXISTS `tab`;
CREATE TABLE `tab` (`ix` INT default 0,`nam` VARCHAR(50) default '' ) CHARACTER SET utf8 COLLATE 'utf8_unicode_ci';
INSERT INTO `tab` VALUES
(1,'á???éí??ňó?????ú y?á ??éí???ó ????ú Y?'),
(2,'aacdeillnoorrstuuyzAACDEILLNOORRSTUUYZ');
SELECT * FROM `tab`;
#SELECT 'á???éí??ňó?????ú y?á ??éí???ó ????ú Y?' AS 'aacdeillnoorrstuuyzAACDEILLNOORRSTUUYZ';
#SHOW COLLATION;
#SHOW CHARACTER SET;
SHOW VARIABLES LIKE 'char%';
是的，腳本文件已轉(zhuǎn)換為 UTF8.現(xiàn)在這里是 my.ini 設(shè)置:
And Yes the script file is converted to UTF8. Now here is my.ini setting:
[mysql]
default-character-set=utf8
[mysqld]
skip-character-set-client-handshake
character-set-server=utf8
collation-server=utf8_unicode_ci
使用 source 此處最終為文件工作，結(jié)果如下:
This finally worked for the file using source here the result:
+------+--------------------------------------------------------------------------+
| ix | nam |
+------+--------------------------------------------------------------------------+
| 1 | á???éí??ňó?????ú y?á ??éí???ó ????ú Y? |
| 2 | aacdeillnoorrstuuyzAACDEILLNOORRSTUUYZ |
+------+--------------------------------------------------------------------------+
+--------------------------+--------------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
+--------------------------+--------------------------------------------------------------------+
推薦答案
您必須在創(chuàng)建表時(shí)指定 CHARACTER SET，最好是在列本身上.否則，您會(huì)從 SHOW VARIABLES LIKE 'char%';
You must specify a CHARACTER SET when creating the table, preferably on the column itself. Otherwise, you get some default from SHOW VARIABLES LIKE 'char%';
SET NAMES 在客戶端建立編碼.
當(dāng) INSERTing 或 SELECTing 時(shí)，編碼從客戶端的編碼 (SET NAMES) 更改為列的 (...VARCHAR ...字符集...).
When INSERTing or SELECTing, the encoding is changed from the client's encoding (SET NAMES) to the column's (... VARCHAR ... CHARACTER SET ...).
你真的需要latin2嗎?世界正在轉(zhuǎn)向 UTF-8.
Do you really need latin2? The world is moving to UTF-8.
這篇關(guān)于使用 source 命令會(huì)破壞非 Unicode 文本編碼的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！