github.com/pingcap/tiflow@v0.0.0-20240520035814-5bf52d54e205/engine/test/integration_tests/dm_sql_mode/data/db1.prepare.sql (about)

     1  set @@session.SQL_MODE='';
     2  
     3  drop database if exists `sql_mode`;
     4  create database `sql_mode`;
     5  use `sql_mode`;
     6  CREATE TABLE `t_1` (
     7    `id` int NOT NULL AUTO_INCREMENT,
     8    `name` varchar(60),
     9    `num` int,
    10    `dt` datetime,
    11    PRIMARY KEY (id)
    12  );
    13  
    14  -- test sql_mode PIPES_AS_CONCAT
    15  insert into t_1(num) values('pipes'||'as'||'concat');
    16  
    17  -- test sql_mode ANSI_QUOTES
    18  insert into t_1(name) values("a");
    19  
    20  -- test sql_mode IGNORE_SPACE
    21  create table count (id int not null, primary key(id));
    22  
    23  -- test sql_mode NO_AUTO_VALUE_ON_ZERO
    24  insert into t_1(id, name) values (10, 'a');
    25  insert into t_1(id, name) values (0, 'b');
    26  insert into t_1(id, name) values (0, 'c');
    27  
    28  -- test sql_mode NO_BACKSLASH_ESCAPES
    29  insert into t_1(name) values ('\\a');
    30  
    31  -- test sql_mode STRICT_TRANS_TABLES && STRICT_ALL_TABLES && NO_ZERO_IN_DATE && NO_ZERO_DATE && ALLOW_INVALID_DATES
    32  insert into t_1(dt) values('0000-06-00');
    33  insert into t_1(dt) values('0000-00-01');
    34  insert into t_1(dt) values('0000-06-01');
    35  insert into t_1(dt) values('0000-00-00');
    36  
    37  -- test sql_mode ERROR_FOR_DIVISION_BY_ZERO
    38  insert into t_1(num) values(4/0);
    39  
    40  -- test sql_mode NO_AUTO_CREATE_USER
    41  drop user if exists 'no_auto_create_user';
    42  grant select on *.* to 'no_auto_create_user';
    43  
    44  -- test different timezone
    45  create table if not exists `sql_mode`.`timezone` (`id` int, `a` timestamp, PRIMARY KEY (id));
    46  set @@session.time_zone = "Asia/Shanghai";
    47  insert into `sql_mode`.`timezone`(`id`, `a`) values (1, '2001-04-15 01:30:12');
    48  set @@session.time_zone = "America/Phoenix";
    49  insert into `sql_mode`.`timezone`(`id`, `a`) values (4, '2001-04-15 01:30:12');
    50  
    51  -- test GBK charset BTW
    52  create table t_3 (id int, name varchar(20), primary key(`id`)) character set gbk;
    53  insert into t_3 (id, name) values (0, '你好0');
    54  insert into t_3 (id, name) values (1, '你好1'), (2, '你好2');
    55  
    56  create table t_5 (id int, name varchar(20) character set gbk, primary key(`id`)) character set utf8;
    57  insert into t_5 (id, name) values (0, '你好0');
    58  insert into t_5 (id, name) values (1, '你好1'), (2, '你好2');