CAST

description

Syntax

T cast (input as Type)

将 input 转成 指定的 Type类型

example

  1. 转常量，或表中某列
  1. mysql> select cast (1 as BIGINT);
  2. +-------------------+
  3. | CAST(1 AS BIGINT) |
  4. +-------------------+
  5. |                 1 |
  6. +-------------------+
  1. 转导入的原始数据
  1. curl --location-trusted -u root: -T ~/user_data/bigint -H "columns: tmp_k1, k1=cast(tmp_k1 as BIGINT)"  http://host:port/api/test/bigint/_stream_load

注：在导入中，由于原始类型均为String，将值为浮点的原始数据做 cast的时候数据会被转换成 NULL ，比如 12.0 。Doris目前不会对原始数据做截断。

如果想强制将这种类型的原始数据 cast to int 的话。请看下面写法：

  1. curl --location-trusted -u root: -T ~/user_data/bigint -H "columns: tmp_k1, k1=cast(cast(tmp_k1 as DOUBLE) as BIGINT)"  http://host:port/api/test/bigint/_stream_load
  3. mysql> select cast(cast ("11.2" as double) as bigint);
  4. +----------------------------------------+
  5. | CAST(CAST('11.2' AS DOUBLE) AS BIGINT) |
  6. +----------------------------------------+
  7. |                                     11 |
  8. +----------------------------------------+
  9. 1 row in set (0.00 sec)

对于DECIMALV3，DATETIME类型，cast会进行四舍五入

  1. mysql> select cast (1.115 as DECIMALV3(16, 2));
  2. +---------------------------------+
  3. | cast(1.115 as DECIMALV3(16, 2)) |
  4. +---------------------------------+
  5. |                            1.12 |
  6. +---------------------------------+
  8. mysql> select cast('2024-12-29-20:40:50.123500' as datetime(3));
  9. +-----------------------------------------------------+
  10. | cast('2024-12-29-20:40:50.123500' as DATETIMEV2(3)) |
  11. +-----------------------------------------------------+
  12. | 2024-12-29 20:40:50.124                             |
  13. +-----------------------------------------------------+
  15. mysql> select cast('2024-12-29-20:40:50.123499' as datetime(3));
  16. +-----------------------------------------------------+
  17. | cast('2024-12-29-20:40:50.123499' as DATETIMEV2(3)) |
  18. +-----------------------------------------------------+
  19. | 2024-12-29 20:40:50.123                             |
  20. +-----------------------------------------------------+

keywords

CAST