MySQL UTF8 Windows导出-Linux导入 [英] MySQL UTF8 Windows export - Linux import
问题描述
我安装了WAMP安装随附的MySQL数据库版本5.1.36.我将其用于Windows XP SP3上的开发目的,并且其中包含一些数据,该数据为西里尔文,并且所有这些表/列的排序规则都设置为utf8_general_ci
.
I have MySQL database version 5.1.36, that came with WAMP installation. I used it for development purposes on Windows XP SP3, and it has some data in it, which is cyrillic, and the collation for all of those tables/columns is set to utf8_general_ci
.
现在该将数据库移至Debian Lenny上的伪生产环境的时候了.这里的MySQL版本是5.0.51a.
Now the time has come to move this database to pseudo-production environment, which is on Debian Lenny. Version of MySQL here is 5.0.51a.
我尝试了以下操作:
- 我使用Windows上的 phpmyadmin 的数据导出了数据库,并将
.sql
文件保存在UTF8中. - 然后,我通过WinSCP(具有默认设置和二进制传输设置)将其传输到Linux计算机.
- 我通过命令行创建数据库:
mysqladmin -u root -p create nbs
-
最后,我尝试创建表并填充数据:
- I exported the databse with data from phpmyadmin on Windows and saved the
.sql
file to be in UTF8. - Then, I transferred it through WinSCP (both with default and binary transfer settings) to Linux machine.
- I created the database through command line:
mysqladmin -u root -p create nbs
Finally, I tried to create tables and fill the data:
mysql -u root -p --default-character-set=utf8 nbs < NBS_utf8_1.sql
但是,这是我得到错误的地方,例如:
However, this is where I'm getting the error, like:
第1行的错误1064(42000):您的SQL语法有错误;检查与您的MySQL服务器版本相对应的手册,以在'附近使用正确的语法 如果不存在,则创建表
`history_members` ( `id`
int(11)NOT NULL AUTO_'在第1行
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' CREATE TABLE IF NOT EXISTS
`history_members` ( `id`
int(11) NOT NULL AUTO_' at line 1
我想编码有些混乱,但我不知道如何以及在哪里.我想我在类似的问题上读过,文本传输的二进制模式只会将换行符CRLF更改为LF(不知道这是否正确...).我在这里想念什么?
Something is messed up with encoding, I suppose... but don't know how and where. I think I read in the similar question on SO that binary mode for text transfer will only change the line breaks CRLF to LF (don't know if this is correct...). What am I missing here?
谢谢.
推荐答案
对于这种特殊情况,通过以下修改解决了该问题:
For this particular case, the problem was solved with the following modifications:
1)我在将目标数据库创建为utf8_general_ci
,
时设置了初始排序规则
2)我通过WinSCP以文本模式传输了文件,
3)我在sql dump的顶部添加了SET NAMES 'utf8' COLLATE 'utf8_general_ci';
.
1) I set initial collation while creating the target database to utf8_general_ci
,
2) I transferred the file with text mode through WinSCP,
3) I added SET NAMES 'utf8' COLLATE 'utf8_general_ci';
to the top of sql dump.
这篇关于MySQL UTF8 Windows导出-Linux导入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!