MySQL 将 int 转换为 varchar
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15368753/
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
Cast int to varchar
提问by Mario
I have below query and need to cast idto varchar
我有以下查询，需要转换id为varchar
Schema
架构
create table t9 (id int, name varchar (55));
insert into t9( id, name)values(2, 'bob');
What I tried
我试过的
select CAST(id as VARCHAR(50)) as col1 from t9;
select CONVERT(VARCHAR(50),id) as colI1 from t9;
but they don't work. Please suggest.
但它们不起作用。请建议。
回答by Taryn
You will need to castor convertas a CHARdatatype, there is no varchardatatype that you can cast/convert data to:
您将需要cast或convert作为CHAR数据类型，没有varchar可以将数据转换/转换为的数据类型：
select CAST(id as CHAR(50)) as col1
from t9;
select CONVERT(id, CHAR(50)) as colI1
from t9;
See the following SQL — in action — over at SQL Fiddle:
在SQL Fiddle 上查看以下 SQL - 实际操作：
/*! Build Schema */
create table t9 (id INT, name VARCHAR(55));
insert into t9 (id, name) values (2, 'bob');
/*! SQL Queries */
select CAST(id as CHAR(50)) as col1 from t9;
select CONVERT(id, CHAR(50)) as colI1 from t9;
Besides the fact that you were trying to convert to an incorrect datatype, the syntax that you were using for convertwas incorrect. The convertfunction uses the following where expris your column or value:
除了您尝试转换为不正确的数据类型这一事实之外，您使用的语法也不convert正确。该convert函数使用以下内容，其中expr是您的列或值：
CONVERT(expr,type)
or
或者
CONVERT(expr USING transcoding_name)
Your original query had the syntax backwards.
您的原始查询的语法向后。
回答by Aaron
You're getting that because VARCHARis not a valid type to cast into. According to the MySQL docs (http://dev.mysql.com/doc/refman/5.5/en/cast-functions.html#function_cast) you can only cast to:
你得到它是因为VARCHAR它不是一个有效的类型。根据 MySQL 文档（http://dev.mysql.com/doc/refman/5.5/en/cast-functions.html#function_cast），您只能转换为：
- BINARY[(N)]
- CHAR[(N)]
- DATE
- DATETIME
- DECIMAL[(M[,D])]
- SIGNED
- [INTEGER]
- TIME
- UNSIGNED [INTEGER]
- 二进制[(N)]
- 字符[(N)]
- 日期
- 约会时间
- 十进制[(M[,D])]
- 签
- [整数]
- 时间
- 无符号 [整数]
I think your best-bet is to use CHAR.
我认为你最好的选择是使用CHAR.
回答by nancy
Yes
是的
SELECT id || '' FROM some_table;
or SELECT id::text FROM some_table;
is postgresql, but mySql doesn't allow that!
是 postgresql，但 mySql 不允许！
short cut in mySql:
mySql 中的快捷方式：
SELECT concat(id, '') FROM some_table;
回答by Andrew Lazarus
I don't have MySQL, but there are RDBMS (Postgres, among others) in which you can use the hack
我没有 MySQL，但有 RDBMS（Postgres 等），您可以在其中使用 hack
SELECT id || '' FROM some_table;
The concatenate does an implicit conversion.
连接进行隐式转换。
回答by user6348455
I solved a problem to comparing a integer Column x a varcharcolumn with
我解决了一个比较整数列 xavarchar列的问题
where CAST(Column_name AS CHAR CHARACTER SET latin1 ) collate latin1_general_ci = varchar_column_name
where CAST(Column_name AS CHAR CHARACTER SET latin1 ) collate latin1_general_ci = varchar_column_name
回答by H.B
I will be answering this in general terms, and very thankful to the above contributers.
I am using MySQL on MySQL Workbench. I had a similar issue trying to concatenate a charand an inttogether using the GROUP_CONCATmethod.
In summary, what has worked for me is this:
let's say your charis 'c' and intis 'i', so, the query becomes:...GROUP_CONCAT(CONCAT(c,' ', CAST(i AS CHAR))...
我将笼统地回答这个问题，非常感谢上述贡献者。
我在 MySQL Workbench 上使用 MySQL。我在尝试使用该方法将 achar和 an连接int在一起时遇到了类似的问题GROUP_CONCAT。总之，对我有用的是：
假设您char是“c”并且int是“i”，因此，查询变为：...GROUP_CONCAT(CONCAT(c,' ', CAST(i AS CHAR))...
回答by John Drinane
Should be able to do something like this also:
也应该能够做这样的事情：
Select (id :> VARCHAR(10)) as converted__id_int
from t9
回答by user2132046
use :
用 ：
SELECT cast(CHAR(50),id) as colI1 from t9;
