SELECT * 在 MySQL 中通过 Microsoft SQL Server 的 ODBC 出现问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4215979/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Problem with SELECT * in MySQL through ODBC from Microsoft SQL Server
提问by Gintautas Miliauskas
I have a MySQL server as a linked server in Microsoft SQL Server 2008. For the link I use MySQL ODBC Connector version 5.1.8. When invoking queries using OPENQUERY(the only way I found of performing queries), problems occur. Simple queries, such as
我有一台 MySQL 服务器作为 Microsoft SQL Server 2008 中的链接服务器。对于链接，我使用 MySQL ODBC 连接器版本 5.1.8。使用OPENQUERY（我发现的执行查询的唯一方法）调用查询时，会出现问题。简单的查询，例如
SELECT * FROM OPENQUERY(MYSQL, 'SHOW TABLES')
work fine. Selection of individual columns, e.g.,
工作正常。选择单个列，例如，
SELECT * FROM OPENQUERY(MYSQL, 'SELECT nr FROM letter')
works fine as well, but SELECT * syntax does not work. The query:
也可以正常工作，但 SELECT * 语法不起作用。查询：
SELECT * FROM OPENQUERY(MYSQL, 'SELECT * FROM mytable')
raises an error:
引发错误：
Msg 7347, Level 16, State 1, Line 6 OLE DB provider 'MSDASQL' for linked server 'MYSQL' returned data that does not match expected data length for column '[MSDASQL].let_nr'. The (maximum) expected data length is 40, while the returned data length is 0.
消息 7347，级别 16，状态 1，第 6 行链接服务器“MYSQL”的 OLE DB 提供程序“MSDASQL”返回的数据与列“[MSDASQL].let_nr”的预期数据长度不匹配。（最大）预期数据长度为 40，而返回的数据长度为 0。
How can I make the SELECT * syntax work?
如何使 SELECT * 语法起作用？
回答by Tarek Basta
I was going through the same problem for 4 days but finally I found the why and how to fix it.
我经历了同样的问题 4 天，但最终我找到了解决它的原因和方法。
This problem happened if you are quering mySQL linked server and the table you query has a datatype char()... This means fixed length NOT varchar(). This happen when your fixed length field has shorter string than the maximum length that sql server expected to get from the odbc.
如果您正在查询 mySQL 链接服务器并且您查询的表的数据类型为 char()，则会发生此问题...这意味着固定长度而不是 varchar()。当您的固定长度字段的字符串比 sql server 预期从 odbc 获得的最大长度短时，就会发生这种情况。
The fix; go to MySQL server and change the datatype to varchar() leaving the length as it is... Example, char(10) change it to varchar(10).
修复；转到 MySQL 服务器并将数据类型更改为 varchar() 保留长度不变...例如，char(10) 将其更改为 varchar(10)。
This will work with no problem.
这将毫无问题地工作。
Please let me know if this fixed it.
请让我知道这是否修复了它。
Tarek Basta
塔瑞克·巴斯塔
回答by Gintautas Miliauskas
Executing the following command before queries seems to help:
在查询之前执行以下命令似乎有帮助：
DBCC TRACEON(8765)
DBCC TRACEON(8765)
The error messages go away and queries seem to be working fine.
错误消息消失，查询似乎工作正常。
I'm not sure what it does though; I found it here: http://bugs.mysql.com/bug.php?id=46857
我不确定它的作用是什么；我在这里找到它：http: //bugs.mysql.com/bug.php?id=46857
Strangely, SQL Server becomes unstable, stops responding to queries and finally crashes with scary-looking dumps in the logs a few minutes after several queries to the MySQL server. I am not sure if this has to do anything with the DBCC command, so I'm still interested in other possible solutions to this problem.
奇怪的是，SQL Server 变得不稳定，停止响应查询，并最终在对 MySQL 服务器进行多次查询后的几分钟内崩溃，并在日志中出现可怕的转储。我不确定这是否与 DBCC 命令有任何关系，所以我仍然对这个问题的其他可能解决方案感兴趣。
回答by Charles
What I did to fix this since I can't modify the MySQL database structure is just create a view with a cast ex: CAST(call_history.calltype AS CHAR(8)) AS Calltype,
and select my view from MSSQL in my linked server.
由于我无法修改 MySQL 数据库结构，我为解决此问题所做的只是创建一个带有强制转换的视图： ex: CAST(call_history.calltype AS CHAR(8)) AS Calltype，然后从链接服务器中的 MSSQL 中选择我的视图。
The reason behind is that some strange types don't work well with the linked server (in my case the MySQL enum)
背后的原因是一些奇怪的类型在链接服务器上不能很好地工作（在我的例子中是 MySQL 枚举）
回答by Jbtatro
Here is a crappy solution I came up with because I am unable to change the datatype to varchar as the db admin for the MySQL server is afraid it will cause issues with his scripts.
这是我想出的一个糟糕的解决方案，因为我无法将数据类型更改为 varchar，因为 MySQL 服务器的数据库管理员担心这会导致他的脚本出现问题。
in my MySQL select query I run a case statement checking the character length of the string and add a filler character in front of the string "filling it up" to the max (in my case its a char(6)). then in the select statement of the openquery I strip the character back off.
在我的 MySQL 选择查询中，我运行一个 case 语句，检查字符串的字符长度，并在字符串“填充它”前面添加一个填充字符到最大值（在我的例子中它是一个字符（6））。然后在 openquery 的 select 语句中我去掉了字符。
Select replace(gradeid,'0','') as gradeid from openquery(LINKEDTOMYSQL, '
SELECT case when char_length(gradeid) = 0 then concat("000000", gradeID)
when char_length(gradeID) = 1 then concat("00000", gradeID)
when char_length(gradeID) = 2 then concat("0000", gradeID)
when char_length(gradeID) = 3 then concat("000", gradeID)
when char_length(gradeID) = 4 then concat("00", gradeID)
when char_length(gradeID) = 5 then concat("0", gradeID)
else gradeid end as gradeid
FROM sometableofmine')
it works but it probably is slower...
它有效，但它可能更慢......
maybe you can make a MySQL function that will do the same logic, or come up with a more elegant solution.
也许您可以制作一个执行相同逻辑的 MySQL 函数，或者提出一个更优雅的解决方案。
回答by Zafor Iqbal
I had the similar problem to this myself, which I resolved by wrapping the column-names in single ` style quotes.
我自己也遇到了类似的问题，我通过将列名包装在单` 样式引号中解决了这个问题。
Instead of...
代替...
column_name
...use...
...用...
`column_name`
Doing this helps the MySql query-engine should the column-name clash with a key or reserved-word.*
如果列名与键或保留字发生冲突，这样做有助于 MySql 查询引擎。*
Instead of using SELECT * FROM TABLE_NAME, try to use all column names with quotes:
不要使用SELECT * FROM TABLE_NAME，而是尝试使用带引号的所有列名：
SELECT `column1`, `column2`, ... FROM TABLE_NAME
Example for normal datatype columns
普通数据类型列的示例
SELECT * FROM OPENQUERY(MYSQL, 'SELECT `column1`, `column2`,...,`columnN` FROM mytable')
Example for ENUM datatype columns
ENUM 数据类型列的示例
SELECT * FROM OPENQUERY(MYSQL, 'SELECT `column1`, trim(`column2`) `column2`, `column3`,...,`columnN` FROM mytable')
*For those used to Sql Server, it is the MySql equivalent of wrapping a value in square-brackets, [and ].
*对于那些习惯使用 Sql Server 的人来说，它是 MySql 的等效项，相当于将一个值包装在方括号中，[而].
回答by cristian
I found this
我找到了这个
"The problem is that one of the fields being returned is a blank or NULL CHAR field. To resolve this in the Mysql ODBC settings select the option "Pad CHAR to Full Length"
“问题是返回的字段之一是空白或 NULL CHAR 字段。要在 Mysql ODBC 设置中解决此问题，请选择“Pad CHAR to Full Length”选项
Look at the last post here
看看这里的最后一个帖子
回答by winston
An alternative would be to use the trim() function in your SELECT statement within OPENQUERY. The downside is you have to list each field individually, but what I did was create a view that calls OPENQUERY and then perfrom select * on the view.
另一种方法是在 OPENQUERY 中的 SELECT 语句中使用 trim() 函数。缺点是您必须单独列出每个字段，但我所做的是创建一个调用 OPENQUERY 的视图，然后在视图上执行选择 *。
Not ideal, but better than changing data types on tables!
不理想，但比更改表上的数据类型要好！
