赞
踩
转载自 MySQL cast()函数
MySQL CAST()
函数的语法如下:
CAST(expression AS TYPE);
CAST()
函数将任何类型的值转换为具有指定类型的值。目标类型可以是以下类型之一:BINARY
,CHAR
,DATE
,DATETIME
,TIME
,DECIMAL
,SIGNED
,UNSIGNED
。
CAST()
函数通常用于返回具有指定类型的值,以便在WHERE,JOIN和HAVING子句中进行比较。
我们来看一下使用CAST()
函数的一些例子。
在下面的例子中,在进行计算之前,MySQL将一个字符串隐式转换成一个整数:
- mysql> SELECT (1 + '1')/2;
- +-------------+
- | (1 + '1')/2 |
- +-------------+
- | 1 |
- +-------------+
- 1 row in set
要将字符串显式转换为整数,可以使用CAST()
函数,如以下语句:
- mysql> SELECT (1 + CAST('1' AS UNSIGNED))/2;
- +-------------------------------+
- | (1 + CAST('1' AS UNSIGNED))/2 |
- +-------------------------------+
- | 1 |
- +-------------------------------+
- 1 row in set
以下语句明确地将整数转换为字符串,并将该字符串与另一个字符串连接:
- mysql> SELECT CONCAT('MySQL CAST example #',CAST(2 AS CHAR));
- +------------------------------------------------+
- | CONCAT('MySQL CAST example #',CAST(2 AS CHAR)) |
- +------------------------------------------------+
- | MySQL CAST example #2 |
- +------------------------------------------------+
- 1 row in set
我们来看看示例数据库(yiibaidb)中的orders
表,其表结构如下 -
- mysql> desc orders;
- +----------------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +----------------+-------------+------+-----+---------+-------+
- | orderNumber | int(11) | NO | PRI | NULL | |
- | orderDate | date | NO | | NULL | |
- | requiredDate | date | NO | | NULL | |
- | shippedDate | date | YES | | NULL | |
- | status | varchar(15) | NO | | NULL | |
- | comments | text | YES | | NULL | |
- | customerNumber | int(11) | NO | MUL | NULL | |
- +----------------+-------------+------+-----+---------+-------+
- 7 rows in set
请参阅以下查询:
- SELECT orderNumber,
- requiredDate
- FROM orders
- WHERE requiredDate BETWEEN '2013-01-01' AND '2013-01-31';
执行上面语句,得到以下结果 -
- +-------------+--------------+
- | orderNumber | requiredDate |
- +-------------+--------------+
- | 10100 | 2013-01-13 |
- | 10101 | 2013-01-18 |
- | 10102 | 2013-01-18 |
- +-------------+--------------+
- 3 rows in set
查询选择要求日期(requiredDate
)在2013年1月的订单。requireDate
列的数据类型为DATE
,因此MySQL必须将文字字符串“2013-01-01”
和“2013-01-31”
在评估WHERE
条件之前转换为TIMESTAMP值 。
但是,为了安全起见,可以使用CAST()
函数将字符串显式转换为TIMESTAMP
值,如下所示:
- SELECT orderNumber,
- requiredDate
- FROM orders
- WHERE requiredDate BETWEEN CAST('2013-01-01' AS DATETIME)
- AND CAST('2013-01-31' AS DATETIME);
执行上面查询语句,得到以下结果 -
- +-------------+--------------+
- | orderNumber | requiredDate |
- +-------------+--------------+
- | 10100 | 2013-01-13 |
- | 10101 | 2013-01-18 |
- | 10102 | 2013-01-18 |
- +-------------+--------------+
- 3 rows in set
以下语句将DOUBLE
值转换为CHAR
值,并将结果用作CONCAT函数的参数:
- SELECT productName,
- CONCAT('Prices(',
- CAST(buyprice AS CHAR),
- ',',
- CAST(msrp AS CHAR),
- ')') prices
- FROM products;
执行上面查询语句,得到以下结果 -
- +---------------------------------------------+-----------------------+
- | productName | prices |
- +---------------------------------------------+-----------------------+
- | 1969 Harley Davidson Ultimate Chopper | Prices(48.81,95.30) |
- | 1952 Alpine Renault 1300 | Prices(98.58,214.30) |
- | 1996 Moto Guzzi 1100i | Prices(68.99,118.94) |
- | 2003 Harley-Davidson Eagle Drag Bike | Prices(91.02,193.66) |
- | 1972 Alfa Romeo GTA | Prices(85.68,136.00) |
- ***************** 此处省略了一大波数据 *******************************************
- | 1982 Camaro Z28 | Prices(46.53,101.15) |
- | ATA: B757-300 | Prices(59.33,118.65) |
- | F/A 18 Hornet 1/72 | Prices(54.40,80.00) |
- | The Titanic | Prices(51.09,100.17) |
- | The Queen Mary | Prices(53.63,99.31) |
- | American Airlines: MD-11S | Prices(36.27,74.03) |
- | Boeing X-32A JSF | Prices(32.77,49.66) |
- | Pont Yacht | Prices(33.30,54.60) |
- +---------------------------------------------+-----------------------+
- 110 rows in set
在本教程中,您学习了如何使用MySQL CAST()
函数将任何类型的值转换成指定类型的值。
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。