如何在MySQL中选择特定数据类型的常量列

I have this (simplified) query, that is not working in MySQL (5.7.17):

我有这个（简化的）查询，在MySQL（5.7.17）中不起作用：

SELECT a.*, CAST(NULL AS BLOB) AS fake_column FROM table1 a

What I am trying to achieve is to SELECT some real columns from a database and add to them a costant column of a specific type.

我想要实现的是从数据库中选择一些真正的列，并向它们添加特定类型的常量列。

This works correctly in SQLite and seems to be the right syntax also in MS-SQL Server, as documented in this question.

这在SQLite中正常工作，并且在MS-SQL Server中似乎也是正确的语法，如本问题中所述。

But in MySQL it fails (just using the MySQL Workbench) with error code 1064 (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 ...). Actually, all of these fail the same way:

但在MySQL中它失败了（只使用MySQL Workbench），错误代码为1064（您的SQL语法中有错误;请查看与您的MySQL服务器版本对应的手册，以便在...附近使用正确的语法）。实际上，所有这些都以同样的方式失败：

CAST(NULL AS BIGINT) AS fake_column
CAST(0 AS BIGINT) AS fake_column
CAST(999 AS BIGINT) AS fake_column
CAST(NULL AS SMALLINT) AS fake_column
CAST(0 AS SMALLINT) AS fake_column
CAST(999 AS SMALLINT) AS fake_column
CAST(NULL AS BIT) AS fake_column
CAST(0 AS BIT) AS fake_column
CAST(1 AS BIT) AS fake_column

The standard CAST and CONVERT functions are supported by MySQL, but there seems to be some limitation with the types they accept, or in the fact I'm casting a constant. With strings it is better. CAST to CHAR(500), for example, works.

MySQL支持标准的CAST和CONVERT函数，但是它们接受的类型似乎存在一些限制，或者我正在构建一个常量。使用字符串更好。例如，CAST到CHAR（500）可以工作。

Are there known workarounds?

有没有已知的解决方法？

Update:

I have tried to define this user function:

我试图定义这个用户函数：

DELIMITER $$

CREATE FUNCTION null_blob RETURNS blob
BEGIN
    RETURN NULL;
END$$

DELIMITER ;

But I still get exactly the same error code 1064.

但我仍然得到完全相同的错误代码1064。

2 个解决方案

#1

There doesn't seem to be a way to cast to BIT, BIGINT, or SMALLINT but you can cast to SIGNED or UNSIGNED (I just tested with MySQL 5.7.7).

似乎没有办法转换为BIT，BIGINT或SMALLINT，但你可以转换为SIGNED或UNSIGNED（我刚用MySQL 5.7.7测试过）。

When you add those constants to real data from other fields, MySQL should automatically treat those constants as the appropriate data type when doing the addition.

当您将这些常量添加到来自其他字段的实际数据时，MySQL应该在添加时自动将这些常量视为适当的数据类型。

Also, see How can I cast an int to a bit in MySQL 5.1?

另外，请参阅如何在MySQL 5.1中将int转换为某个位？

#2

Did you try CONVERT instead ?

你有没有试过CONVERT？

https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html#function_convert

#1