SQL Server将varchar数据转储到mysql中的文件加载而不是mariadb中 [英] SQL Server dump of varchar data to file loads in mysql but not mariadb
问题描述
我通过cygwin命令行使用SQLCMD将许多字符串数据从SQL Server数据库中以INSERTS格式转储到Windows10工作站上的文本文件中.
I dumped a lot of string data from a SQL Server database into a text file as INSERTS on my Windows10 workstation using the SQLCMD via the cygwin command line.
它可以很好地加载到mysql中，但是在mariadb中，我会收到此错误:
It loads into mysql fine, but in mariadb I get this error:
ERROR 1366 (22007) at line 384:
Incorrect string value: '\x87ao Su...' for column 'description' at row 1995
我的SQL Server数据库具有以下设置:
My SQL Server DB has these settings:
SELECT DISTINCT C.collation_name
FROM sys.tables AS T
INNER JOIN sys.columns C
ON T.object_id = C.object_id
WHERE collation_name IS NOT NULL;
collation_name: Latin1_General_CI_AS
SELECT DISTINCT C.collation_name
FROM sys.tables AS T
INNER JOIN sys.columns C
ON T.object_id = C.object_id
WHERE collation_name IS NOT NULL;
collation_name: Latin1_General_CI_AS
这是MySQL(愉快地插入其中):
and this is MySQL (where it inserts happily):
SHOW FULL COLUMNS FROM forecast
--------------
+--------------------+------------------+-----------------+------+-----+-------------------+----------------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+--------------------+------------------+-----------------+------+-----+-------------------+----------------+---------------------------------+---------+
| description | varchar(255) | utf8_unicode_ci | YES | | NULL | | select,insert,update,references | |
SELECT @@character_set_database, @@collation_database, @@collation_connection
--------------
+--------------------------+----------------------+------------------------+
| @@character_set_database | @@collation_database | @@collation_connection |
+--------------------------+----------------------+------------------------+
| utf8 | utf8_unicode_ci | utf8_general_ci |
+--------------------------+----------------------+------------------------+
1 row in set (0.01 sec)
这是从MariaDB发出的上述错误:
and this is from MariaDB where it gives that error above:
SHOW FULL COLUMNS FROM forecast
--------------
+--------------------+------------------+-----------------+------+-----+---------------------+----------------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+--------------------+------------------+-----------------+------+-----+---------------------+----------------+---------------------------------+---------+
| description | varchar(255) | utf8_unicode_ci | YES | | NULL | | select,insert,update,references | |
+--------------------+------------------+-----------------+------+-----+---------------------+----------------+---------------------------------+---------+
10 rows in set (0.01 sec)
MariaDB [tardis]> SELECT @@character_set_database, @@collation_database, @@collation_connection;
--------------
SELECT @@character_set_database, @@collation_database, @@collation_connection
--------------
+--------------------------+----------------------+------------------------+
| @@character_set_database | @@collation_database | @@collation_connection |
+--------------------------+----------------------+------------------------+
| utf8 | utf8_unicode_ci | utf8_general_ci |
+--------------------------+----------------------+------------------------+
1 row in set (0.01 sec)
据我所知，MariaDB的行为应与MySQL相同，但事实并非如此.
As far as I can tell, MariaDB should behave the same as MySQL but it doesn't.
我该怎么办?
我可以使用SQL Server函数转换文本-目前，我只是转义\'字符-但是如何?
I could convert the text with a SQL Server function - at the moment I am just escaping \' characters - but how?
[UPDATE] 刚发现从文件运行import SQL命令时，MariaDB的数据没有任何问题.
[UPDATE] Just discovered that MariaDB doesn't have any problems with the data when I run the import SQL commands from file.
仅当我将其直接从SQLCMD命令行传递到mysql.exe时，才会出现错误:
It's only when I pipe it directly on the command line from SQLCMD to mysql.exe that I get the error:
$ SQLCMD.EXE -i "c:\dev\workspace\sql\generate-inserts-for-migration.sql" \
-h-1 -S "myhost" -d MyDBName -r1 -W \
-b -f o:65001 | mysql -h myhost.amazonaws.com -u adam -p
我想这样做，这样就不必在硬盘上创建10GB的SQL文件，也可以在同一网络上的docker容器(GB空间有限)中运行它.云.
I wanted to do it like that so that I don't have to create a 10GB SQL file on my hard drive, or I could even run it in a docker container (with limited GB space) on the same network on the cloud.
推荐答案
与Curaçao
在转储文件中看起来像这样
which looked like this in the dumped file
Cura<87>ao
使用SQLCMD -f o:65001从SQL Server导出该文件后，其中65001是Microsoft UTF8代码页.
after exporting it from SQL Server with SQLCMD -f o:65001 where 65001 is the Microsoft UTF8 code page.
我也尝试过SQLCMD -f o:1252，因为数据库说它是latin1.
I also tried SQLCMD -f o:1252 since the database says it's latin1.
I discovered what the special character was thanks to this excellent answer How can I find Unicode/non-ASCII characters in an NTEXT field in a SQL Server 2005 table?
并且我认为mySQL(与MariaDB相对)只是在没有抱怨的情况下严重导入了它.
and I assume that mySQL (as opposed to MariaDB) was simply importing it badly without complaint.
MariaDB文档告诉我设置--default-character-set在命令行上适当显示，因此我尝试了latin1和utf8，但两者都不起作用.
The MariaDB docs told me to set the --default-character-set on the command line appropriately, so I tried latin1 and utf8, neither of which worked.
然后，我查看了/usr/share/mysql/charsets/Index.xml中一长串用于MariaDB的字符集，结果发现cp1250可以正常工作，但它会将特殊字符导入为‡
I then looked at the long list of character sets for MariaDB in /usr/share/mysql/charsets/Index.xml and it turns out the cp1250 works without complaint but it imports the special character as ‡
命令为mysql --default-character-set=cp1250.
我无法正确导入数据，但至少现在我已经将其导入MariaDB中.
I haven't been able to import the data correctly, but at least I've got it to import in MariaDB now.
这篇关于SQL Server将varchar数据转储到mysql中的文件加载而不是mariadb中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
