MySQL 将十进制转换为 INT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8537037/
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 DECIMAL to INT
提问by Drahcir
I'm trying to do this:
我正在尝试这样做：
SELECT CAST(columnName AS INT), moreColumns, etc
FROM myTable
WHERE ...
I've looked at the help FAQs here: http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html, it says I can do it like CAST(val AS TYPE), but it's not working.
我在这里查看了帮助常见问题解答：http: //dev.mysql.com/doc/refman/5.0/en/cast-functions.html，它说我可以这样做CAST(val AS TYPE)，但它不起作用。
Trying to convert a decimal to int, real value is 223.00 and I want 223
试图将十进制转换为 int，实际值为 223.00，我想要 223
回答by RolandoMySQLDBA
You could try the FLOORfunction like this:
你可以像这样尝试FLOOR函数：
SELECT FLOOR(columnName), moreColumns, etc
FROM myTable
WHERE ...
You could also try the FORMATfunction, provided you know the decimal places can be omitted:
您也可以尝试FORMAT函数，前提是您知道可以省略小数位：
SELECT FORMAT(columnName,0), moreColumns, etc
FROM myTable
WHERE ...
You could combine the two functions
你可以结合这两个功能
SELECT FORMAT(FLOOR(columnName),0), moreColumns, etc
FROM myTable
WHERE ...
回答by fredley
From the article you linked to:
从您链接到的文章：
The type can be one of the following values:
BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL[(M[,D])]
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]
类型可以是以下值之一：
二进制[(N)]
字符[(N)]
日期
约会时间
十进制[(M[,D])]
签名 [整数]
时间
无符号 [整数]
Try SIGNEDinstead of INT
尝试SIGNED代替INT
回答by David Strencsev
A more optimized way in mysqlfor this purpose*:
为此目的，mysql 中一种更优化的方式*：
SELECT columnName DIV 1 AS columnName, moreColumns, etc
FROM myTable
WHERE ...
Using DIV1is a huge speed improvementover FLOOR, not to mention string based functions like FORMAT
使用DIV1是对FLOOR的巨大速度改进，更不用说像FORMAT这样的基于字符串的函数了
mysql> SELECT BENCHMARK(10000000,1234567 DIV 7) ;
+-----------------------------------+
| BENCHMARK(10000000,1234567 DIV 7) |
+-----------------------------------+
| 0 |
+-----------------------------------+
1 row in set (0.83 sec)
mysql> SELECT BENCHMARK(10000000,1234567 / 7) ;
+---------------------------------+
| BENCHMARK(10000000,1234567 / 7) |
+---------------------------------+
| 0 |
+---------------------------------+
1 row in set (7.26 sec)
mysql> SELECT BENCHMARK(10000000,FLOOR(1234567 / 7)) ;
+----------------------------------------+
| BENCHMARK(10000000,FLOOR(1234567 / 7)) |
+----------------------------------------+
| 0 |
+----------------------------------------+
1 row in set (8.80 sec)
(*) NOTE: As pointed by Grbts, be aware of the behaviour of DIV 1 when used with non unsigned/positive values.
(*) 注意：正如 Grbts 所指出的，当与非无符号/正值一起使用时，请注意 DIV 1 的行为。
回答by Grbts
There is an important difference between floor() and DIV 1. For negative numbers, they behave differently. DIV 1 returns the integer part (as cast as signed does), while floor(x) returns "the largest integer value not greater than x" (from the manual). So : select floor(-1.1)results in -2, while select -1.1 div 1results in -1
floor() 和 DIV 1 之间有一个重要的区别。对于负数，它们的行为不同。DIV 1 返回整数部分（正如有符号的那样），而 floor(x) 返回“不大于 x 的最大整数值”（来自手册）。所以：选择 floor(-1.1)结果为 -2，而选择 -1.1 div 1结果为 -1
回答by basic6
The CAST() function does not support the "official" data type "INT" in MySQL, it's not in the list of supported types. With MySQL, "SIGNED" (or "UNSIGNED") could be used instead:
CAST() 函数不支持 MySQL 中的“官方”数据类型“INT”，它不在支持的类型列表中。对于 MySQL，可以使用“SIGNED”（或“UNSIGNED”）代替：
CAST(columnName AS SIGNED)
However, this seems to be MySQL-specific (not standardized), so it may not work with other databases. At least this document (Second Informal Review Draft) ISO/IEC 9075:1992, Databasedoes not list "SIGNED"/"UNSIGNED" in section 4.4 Numbers.
但是，这似乎是特定于 MySQL 的（未标准化），因此它可能不适用于其他数据库。至少本文档（第二次非正式草案）ISO/IEC 9075:1992，数据库在第 1 节中未列出“已签名”/“未签名” 4.4 Numbers。
But DECIMAL is both standardized and supported by MySQL, so the following should work for MySQL (tested) and other databases:
但是 DECIMAL 既标准化又受 MySQL 支持，因此以下内容应该适用于 MySQL（已测试）和其他数据库：
CAST(columnName AS DECIMAL(0))
According to the MySQL docs:
根据MySQL 文档：
If the scale is 0, DECIMAL values contain no decimal point or fractional part.
如果小数位数为 0，则 DECIMAL 值不包含小数点或小数部分。
回答by Jamie Brown
There's also ROUND() if your numbers don't necessarily always end with .00. ROUND(20.6) will give 21, and ROUND(20.4) will give 20.
如果您的数字不一定总是以 .00 结尾，则还有 ROUND()。ROUND(20.6) 将给出 21，ROUND(20.4) 将给出 20。
回答by mshabab
your can try this :
你可以试试这个：
SELECT columnName1, CAST(columnName2 AS SIGNED ) FROM tableName
回答by bbe
1 cent: no space b/w CAST and (expression). i.e., CAST(columnName AS SIGNED).
1 分：没有空格黑白 CAST 和（表达式）。即，CAST(columnName AS SIGNED)。
回答by ajreal
Try cast (columnName as unsigned)
尝试 cast (columnName as unsigned)
unsigned is positive value only
无符号仅是正值
If you want to include negative value, then cast (columnName as signed),
The difference between sign (negative include) and unsigned (twice the size of sign, but non-negative)
如果要包含负值，则cast (columnName as signed),
符号（负包含）和无符号（符号大小的两倍，但非负）之间的区别
