在 MySQL 中 CAST 到 DECIMAL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11830509/
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 to DECIMAL in MySQL
提问by Ben
I am trying to cast to Decimal in MySQL like this:
我正在尝试像这样在 MySQL 中转换为 Decimal：
CAST((COUNT(*) * 1.5) AS DECIMAL(2))
I'm trying to convert the number of rows in a table (times 1.5) to a floating point number with two digits after the point.
我正在尝试将表中的行数（乘以 1.5）转换为该点后有两位数的浮点数。
SQL code:
SQL 代码：
SELECT CONCAT(Guardian.title, ' ',
Guardian.forename, ' ',
Guardian.surname) AS 'Guardian Name',
COUNT(*) AS 'Number of Activities',
(COUNT(*) * 1.5) AS 'Cost'
FROM Schedule
INNER JOIN Child ON Schedule.child_id = Child.id
INNER JOIN Guardian ON Child.guardian = Guardian.id
GROUP BY Guardian
ORDER BY Guardian.surname, Guardian.forename ASC
It produces an error:
它产生一个错误：
#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 'CAST((COUNT(*) * 1.5) AS DECIMAL(12,2))' at line 1.
Another try, this cast also doesn't work:
再试一次，这个演员也不起作用：
SELECT CONCAT(Guardian.title, ' ',
Guardian.forename, ' ',
Guardian.surname) AS 'Guardian Name',
COUNT(*) AS 'Number of Activities',
CAST((COUNT(*) * 1.5) AS DECIMAL(8,2)) AS 'Cost'
FROM Schedule
INNER JOIN Child ON Schedule.child_id = Child.id
INNER JOIN Guardian ON Child.guardian = Guardian.id
GROUP BY Guardian
ORDER BY Guardian.surname, Guardian.forename ASC
How do I use mysql to cast from integer to decimal?
如何使用 mysql 将整数转换为小数？
回答by ypercube??
From MySQL docs: Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC:
来自 MySQL 文档：Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC：
In standard SQL, the syntax
DECIMAL(M)is equivalent toDECIMAL(M,0)
在标准 SQL 中，语法
DECIMAL(M)等效于DECIMAL(M,0)
So, you are converting to a number with 2 integer digits and 0 decimal digits. Try this instead:
因此，您正在转换为具有 2 个整数位和 0 个小数位的数字。试试这个：
CAST((COUNT(*) * 1.5) AS DECIMAL(12,2))
回答by Eric Leschinski
MySQL casts to Decimal:
MySQL 转换为十进制：
Cast bare integer to decimal:
将裸整数转换为十进制：
select cast(9 as decimal(4,2)); //prints 9.00
Cast Integers 8/5 to decimal:
将整数 8/5 转换为十进制：
select cast(8/5 as decimal(11,4)); //prints 1.6000
Cast string to decimal:
将字符串转换为十进制：
select cast(".885" as decimal(11,3)); //prints 0.885
Cast two int variables into a decimal
将两个 int 变量转换为十进制
mysql> select 5 into @myvar1;
Query OK, 1 row affected (0.00 sec)
mysql> select 8 into @myvar2;
Query OK, 1 row affected (0.00 sec)
mysql> select @myvar1/@myvar2; //prints 0.6250
Cast decimal back to string:
将十进制转换回字符串：
select cast(1.552 as char(10)); //shows "1.552"
回答by John Woo
DECIMALhas two parts: Precisionand Scale. So part of your query will look like this:
DECIMAL有两部分：Precision和Scale。因此，您的查询的一部分将如下所示：
CAST((COUNT(*) * 1.5) AS DECIMAL(8,2))
Precisionrepresents the number of significant digits that are stored for values. Scalerepresents the number of digits that can be stored following the decimal point.
Precision表示为值存储的有效位数。Scale表示小数点后可以存储的位数。
回答by Jake
回答by Rodério Kunz
If you need a lot of decimal numbers, in this example 17, I share with you MySql code:
如果你需要很多十进制数，在这个例子17中，我和你分享MySql代码：
This is the calculate:
这是计算：
=(9/1147)*100
SELECT TRUNCATE(((CAST(9 AS DECIMAL(30,20))/1147)*100),17);
=(9/1147)*100
SELECT TRUNCATE(((CAST(9 AS DECIMAL(30,20))/1147)*100),17);
