严格模式(strict_mode)为导入操作中的一个参数配置。该参数会影响某些数值的导入行为和最终导入的数据。

本文档主要说明如何设置严格模式,以及严格模式产生的影响。

如何设置

严格模式默认情况下都为 False,即关闭状态。

不同的导入方式设置严格模式的方式不尽相同。

  1. BROKER LOAD

    1. LOAD LABEL example_db.label1
    2. (
    3. DATA INFILE("bos://my_bucket/input/file.txt")
    4. INTO TABLE `my_table`
    5. COLUMNS TERMINATED BY ","
    6. )
    7. WITH BROKER bos
    8. (
    9. "bos_endpoint" = "http://bj.bcebos.com",
    10. "bos_accesskey" = "xxxxxxxxxxxxxxxxxxxxxxxxxx",
    11. "bos_secret_accesskey"="yyyyyyyyyyyyyyyyyyyyyyyyyy"
    12. )
    13. PROPERTIES
    14. (
    15. "strict_mode" = "true"
    16. )
  2. STREAM LOAD

    1. curl --location-trusted -u user:passwd \
    2. -H "strict_mode: true" \
    3. -T 1.txt \
    4. http://host:port/api/example_db/my_table/_stream_load
  3. ROUTINE LOAD

    1. CREATE ROUTINE LOAD example_db.test_job ON my_table
    2. PROPERTIES
    3. (
    4. "strict_mode" = "true"
    5. )
    6. FROM KAFKA
    7. (
    8. "kafka_broker_list" = "broker1:9092,broker2:9092,broker3:9092",
    9. "kafka_topic" = "my_topic"
    10. );
  4. INSERT

    通过会话变量设置:

    1. SET enable_insert_strict = true;
    2. INSERT INTO my_table ...;

严格模式的作用

对于导入过程中的列类型转换进行严格过滤。

严格过滤的策略如下:

对于列类型转换来说,如果开启严格模式,则错误的数据将被过滤。这里的错误数据是指:原始数据并不为 null,而在进行列类型转换后结果为 null 的这一类数据。

这里说指的 列类型转换,并不包括用函数计算得出的 null 值。

对于导入的某列类型包含范围限制的,如果原始数据能正常通过类型转换,但无法通过范围限制的,严格模式对其也不产生影响。例如:如果类型是 decimal(1,0), 原始数据为 10,则属于可以通过类型转换但不在列声明的范围内。这种数据 strict 对其不产生影响。

1. 以列类型为 TinyInt 来举例:

原始数据类型原始数据举例转换为 TinyInt 后的值严格模式结果
空值\NNULL开启或关闭NULL
非空值“abc” or 2000NULL开启非法值(被过滤)
非空值“abc”NULL关闭NULL
非空值11开启或关闭正确导入

严格模式 - 图1提示

说明:

  1. 表中的列允许导入空值

  2. abc2000 在转换为 TinyInt 后,会因类型或精度问题变为 NULL。在严格模式开启的情况下,这类数据将会被过滤。而如果是关闭状态,则会导入 null

2. 以列类型为 Decimal(1,0) 举例

原始数据类型原始数据举例转换为 Decimal 后的值严格模式结果
空值\Nnull开启或关闭NULL
非空值aaaNULL开启非法值(被过滤)
非空值aaaNULL关闭NULL
非空值1 or 101 or 10开启或关闭正确导入

严格模式 - 图2提示

说明:

  1. 表中的列允许导入空值

  2. abc 在转换为 Decimal 后,会因类型问题变为 NULL。在严格模式开启的情况下,这类数据将会被过滤。而如果是关闭状态,则会导入 null

  3. 10 虽然是一个超过范围的值,但是因为其类型符合 decimal 的要求,所以严格模式对其不产生影响。10 最后会在其他导入处理流程中被过滤。但不会被严格模式过滤。

限定部分列更新只能更新已有的列

在严格模式下,部分列更新插入的每一行数据必须满足该行数据的 Key 在表中已经存在。而在而非严格模式下,进行部分列更新时可以更新 Key 已经存在的行,也可以插入 Key 不存在的新行。

例如有表结构如下:

  1. mysql> desc user_profile;
  2. +------------------+-----------------+------+-------+---------+-------+
  3. | Field | Type | Null | Key | Default | Extra |
  4. +------------------+-----------------+------+-------+---------+-------+
  5. | id | INT | Yes | true | NULL | |
  6. | name | VARCHAR(10) | Yes | false | NULL | NONE |
  7. | age | INT | Yes | false | NULL | NONE |
  8. | city | VARCHAR(10) | Yes | false | NULL | NONE |
  9. | balance | DECIMALV3(9, 0) | Yes | false | NULL | NONE |
  10. | last_access_time | DATETIME | Yes | false | NULL | NONE |
  11. +------------------+-----------------+------+-------+---------+-------+

表中有一条数据如下:

  1. 1,"kevin",18,"shenzhen",400,"2023-07-01 12:00:00"

当用户使用非严格模式的 Stream Load 部分列更新向表中插入如下数据时

  1. 1,500,2023-07-03 12:00:01
  2. 3,23,2023-07-03 12:00:02
  3. 18,9999999,2023-07-03 12:00:03
  1. curl --location-trusted -u root -H "partial_columns:true" -H "strict_mode:false" -H "column_separator:," -H "columns:id,balance,last_access_time" -T /tmp/test.csv http://host:port/api/db1/user_profile/_stream_load

表中原有的一条数据将会被更新,此外还向表中插入了两条新数据。对于插入的数据中用户没有指定的列,如果该列有默认值,则会以默认值填充;否则,如果该列可以为 NULL,则将以 NULL 值填充;否则本次插入不成功。

而当用户使用严格模式的 Stream Load 部分列更新向表中插入上述数据时

  1. curl --location-trusted -u root -H "partial_columns:true" -H "strict_mode:true" -H "column_separator:," -H "columns:id,balance,last_access_time" -T /tmp/test.csv http://host:port/api/db1/user_profile/_stream_load

此时,由于开启了严格模式且第二、三行的数据的 key((3), (18)) 不在原表中,所以本次导入会失败。