通过 Microsoft SQL Server 的 ODBC 在 MySQL 中进行 SELECT * 时出现问题

Question

我有一个 MySQL 服务器作为 Microsoft SQL Server 2008 中的链接服务器。对于链接，我使用 MySQL ODBC 连接器版本 5.1.8。当使用OPENQUERY（我发现执行查询的唯一方法）调用查询时，会出现问题。简单的查询，如

SELECT * FROM OPENQUERY(MYSQL, 'SHOW TABLES')

工作正常。例如，选择单个列

SELECT * FROM OPENQUERY(MYSQL, 'SELECT nr FROM letter')

也可以正常工作，但 SELECT * 语法不起作用。查询：

SELECT * FROM OPENQUERY(MYSQL, 'SELECT * FROM mytable')

引发错误：

消息 7347，16 级，状态 1，第 6 行 用于链接的 OLE DB 提供程序“MSDASQL” 服务器“MYSQL”返回的数据确实 与预期数据长度不匹配 列“[MSDASQL].let_nr”。这 （最大）预期数据长度为 40， 而返回的数据长度为0。

如何使 SELECT * 语法起作用？

Answer 1

如果您正在查询 MySQL 链接服务器并且您查询的表具有数据类型 char()，则会出现此问题。这意味着固定长度而不是 varchar()。当固定长度字段的字符串比 SQL Server 期望从 ODBC 获取的最大长度短时，就会发生这种情况。

要解决此问题，请转到 MySQL 服务器并将数据类型更改为 varchar() 并保持长度不变。示例将 char(10) 更改为 varchar(10)。

Answer 2

在查询之前执行以下命令似乎有帮助：

DBCC TRACEON(8765)

错误消息消失，查询似乎工作正常。

但我不确定它的作用；我在这里找到它： http://bugs.mysql.com/bug.php?id=46857

奇怪的是，在对 MySQL 服务器进行多次查询后几分钟，SQL Server 变得不稳定，停止响应查询，并最终崩溃，并在日志中出现看起来可怕的转储。我不确定这是否与 DBCC 命令有任何关系，所以我仍然对该问题的其他可能解决方案感兴趣。

Answer 3

由于我无法修改 MySQL 数据库结构，我所做的解决方法就是创建一个带有强制转换的视图：CAST(call_history.calltype AS CHAR(8)) AS Calltype,
并从我的链接服务器中的 MSSQL 选择我的视图。

背后的原因是一些奇怪的类型不能很好地与链接服务器一起工作（在我的例子中是 MySQL 枚举）

Answer 4

我找到了这个

“问题是其中一个字段
返回的是空白或 NULL CHAR
场地。在Mysql中解决这个问题
ODBC 设置选择选项“Pad
CHAR 到全长”

请参阅此处的最后一篇文章

Answer 5

另一种方法是在 OPENQUERY 中的 SELECT 语句中使用 trim() 函数。缺点是您必须单独列出每个字段，但我所做的是创建一个调用 OPENQUERY 的视图，然后在视图上执行 select * 。

并不理想，但比更改表上的数据类型要好！

Answer 6

这是我想出的一个糟糕的解决方案，因为我无法将数据类型更改为 varchar，因为 MySQL 服务器的数据库管理员担心这会导致他的脚本出现问题。

在我的 MySQL 选择查询中，我运行一个 case 语句检查字符串的字符长度，并在字符串前面添加一个填充字符“填充”到最大值（在我的例子中是 char(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')

它可以工作，但可能会慢一些...

也许你可以创建一个 MySQL 函数来执行相同的逻辑，或者提出一个更优雅的解决方案。

Answer 7

我自己也遇到了类似的问题，我通过将列名括在单`样式引号中解决了这个问题。

而不是...

column_name

...使用...

`column_name`

如果列名与键或保留字冲突，这样做有助于 MySql 查询引擎。*

而不是使用 SELECT * FROM TABLE_NAME，尝试使用带引号的所有列名：

SELECT `column1`, `column2`, ... FROM TABLE_NAME

普通数据类型列的示例

SELECT * FROM OPENQUERY(MYSQL, 'SELECT `column1`, `column2`,...,`columnN` FROM mytable')

ENUM 数据类型列的示例

SELECT * FROM OPENQUERY(MYSQL, 'SELECT `column1`, trim(`column2`) `column2`, `column3`,...,`columnN` FROM mytable')

*对于那些用于 Sql Server 的列，它是 MySql 相当于包装值在方括号中，[ 和 ]。