MySQL中的CAST函数用于将一个数据类型转换为另一个数据类型,它可以将一个值从一个数据类型转换为另一个数据类型,同时保持其原始值不变,CAST函数的语法如下:
CAST(expression AS type)
expression
是要转换的值,type
是目标数据类型。
下面详细介绍一下CAST函数的具体使用。
1、整数类型之间的转换
将整数值转换为较小的整数类型:
```sql
CAST(123456789 AS SIGNED) -> -123456789
CAST(123456789 AS UNSIGNED) -> 123456789
```
将整数值转换为较大的整数类型:
```sql
CAST(123456789 AS BIGINT) -> 123456789
```
将整数值转换为浮点数类型:
```sql
CAST(123456789 AS FLOAT) -> 123456789.0
CAST(123456789 AS DOUBLE) -> 123456789.0
```
2、浮点数类型之间的转换
将浮点数值转换为较小的浮点数类型:
```sql
CAST(123456789.123456789 AS DECIMAL(10, 2)) -> 123456789.12
```
将浮点数值转换为较大的浮点数类型:
```sql
CAST(123456789.123456789 AS DECIMAL(19, 4)) -> 123456789.1235
```
将浮点数值转换为整数类型:
```sql
CAST(123456789.123456789 AS SIGNED) -> -123456789
CAST(123456789.123456789 AS UNSIGNED) -> 123456789
```
3、字符串类型之间的转换
将字符串转换为整数类型:
```sql
CAST('123' AS SIGNED) -> 123
CAST('123' AS UNSIGNED) -> 123
```
将字符串转换为浮点数类型:
```sql
CAST('123.45' AS FLOAT) -> 123.45
CAST('123.45' AS DOUBLE) -> 123.45
```
将字符串转换为日期和时间类型:
```sql
CAST('2022-01-01' AS DATE) -> '2022-01-01'
CAST('12:34:56' AS TIME) -> '12:34:56'
CAST('2022-01-01 12:34:56' AS DATETIME) -> '2022-01-01 12:34:56'
```
将其他数据类型转换为字符串类型:
```sql
CAST(123 AS CHAR) -> '1'
CAST(NULL AS CHAR) -> ''
```
4、日期和时间类型之间的转换
将日期和时间值转换为较小的日期和时间类型:
```sql
CAST('2022-01-01' AS DATE) -> '2022-01-01'
CAST('12:34:56' AS TIME) -> '12:34:56'
```
将日期和时间值转换为较大的日期和时间类型:
```sql
CAST('2022-01-01' AS DATETIME) -> '2022-01-01 00:00:00'
CAST('12:34:56' AS TIMESTAMP) -> '2022-01-01 00:00:00'
```
将日期和时间值转换为字符串类型:
```sql
CAST('2022-01-01' AS CHAR) -> 'YYYY-MM-DD'
CAST('12:34:56' AS CHAR) -> 'HH:MI:SS'
CAST('2022-01-01 12:34:56' AS CHAR) -> 'YYYY-MM-DD HH:MI:SS'
```
将其他数据类型转换为日期和时间类型:
```sql
CAST('YYYY-MM-DD' AS DATE) -> 'YYYY-MM-DD'
CAST('HH:MI:SS' AS TIME) -> 'HH:MI:SS'
CAST('YYYY-MM-DD HH:MI:SS' AS DATETIME) -> 'YYYY-MM-DD HH:MI:SS'
```
原创文章,作者:K-seo,如若转载,请注明出处:https://www.kdun.cn/ask/354889.html