从小数到整数的转换

我正在努力:

SELECT CAST(columnName AS INT), moreColumns, etc
FROM myTable
WHERE ...

我看了这里的帮助常见问题: http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html，它说我可以做它喜欢 CAST(val AS TYPE)，但它不工作。

试着把一个小数转换成整数，实际值是223.00，我想要223

224183 次浏览
小开

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]

Try SIGNED instead of INT

小开

Try 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)

小开
最佳答案

You could try the FLOOR function like this:

SELECT FLOOR(columnName), moreColumns, etc
FROM myTable
WHERE ...

You could also try the FORMAT function, provided you know the decimal places can be omitted:

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 ...
小开

A more optimized way in for this purpose*:

SELECT columnName DIV 1 AS columnName, moreColumns, etc
FROM myTable
WHERE ...

Using DIV 1 is a huge speed improvement over FLOOR, not to mention string based functions like FORMAT

Speed of MySQL integer division Bar Chart (graphic from Roland Bouman's blog)

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.

小开

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 1 results in -1

小开

use this

mysql> SELECT TRUNCATE(223.69, 0);
> 223

Here's a link

小开

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.

小开

your can try this :

SELECT columnName1, CAST(columnName2 AS  SIGNED ) FROM tableName
小开

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(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, Database does not list "SIGNED"/"UNSIGNED" in section 4.4 Numbers.

But DECIMAL is both standardized and supported by MySQL, so the following should work for MySQL (tested) and other databases:

CAST(columnName AS DECIMAL(0))

According to the MySQL docs:

If the scale is 0, DECIMAL values contain no decimal point or fractional part.

小开

1 cent: no space b/w CAST and (expression). i.e., CAST(columnName AS SIGNED).


提交答案