2446 lines
76 KiB
Plaintext
2446 lines
76 KiB
Plaintext
set tidb_enable_clustered_index = on;
|
|
drop table if exists t;
|
|
create table t(a char(20), b int, primary key(a));
|
|
insert into t values('aa', 1), ('bb', 1);
|
|
insert into t values('aa', 2);
|
|
Error 1062 (23000): Duplicate entry 'aa' for key 't.PRIMARY'
|
|
drop table t;
|
|
create table t(a char(20), b varchar(30), c varchar(10), primary key(a, b, c));
|
|
insert into t values ('a', 'b', 'c'), ('b', 'a', 'c');
|
|
insert into t values ('a', 'b', 'c');
|
|
Error 1062 (23000): Duplicate entry 'a-b-c' for key 't.PRIMARY'
|
|
set tidb_enable_clustered_index = default;
|
|
set tidb_enable_clustered_index = on;
|
|
drop table if exists t1;
|
|
create table t1(c1 decimal(6,4), primary key(c1));
|
|
insert into t1 set c1 = 0.1;
|
|
insert into t1 set c1 = 0.1 on duplicate key update c1 = 1;
|
|
select * from t1;
|
|
c1
|
|
1.0000
|
|
set tidb_enable_clustered_index = default;
|
|
drop table if exists t1;
|
|
create table t1(c1 year);
|
|
insert into t1 set c1 = '2004';
|
|
alter table t1 add index idx(c1);
|
|
delete from t1;
|
|
admin check table t1;
|
|
drop table if exists t1;
|
|
create table t1(c1 year);
|
|
insert into t1 set c1 = 2004;
|
|
alter table t1 add index idx(c1);
|
|
delete from t1;
|
|
admin check table t1;
|
|
drop table if exists t1;
|
|
create table t1(c1 bit);
|
|
insert into t1 set c1 = 1;
|
|
alter table t1 add index idx(c1);
|
|
delete from t1;
|
|
admin check table t1;
|
|
drop table if exists t1;
|
|
create table t1(c1 smallint unsigned);
|
|
insert into t1 set c1 = 1;
|
|
alter table t1 add index idx(c1);
|
|
delete from t1;
|
|
admin check table t1;
|
|
drop table if exists t1;
|
|
create table t1(c1 int unsigned);
|
|
insert into t1 set c1 = 1;
|
|
alter table t1 add index idx(c1);
|
|
delete from t1;
|
|
admin check table t1;
|
|
drop table if exists t1;
|
|
create table t1(c1 smallint);
|
|
insert into t1 set c1 = -1;
|
|
alter table t1 add index idx(c1);
|
|
delete from t1;
|
|
admin check table t1;
|
|
drop table if exists t1;
|
|
create table t1(c1 int);
|
|
insert into t1 set c1 = -1;
|
|
alter table t1 add index idx(c1);
|
|
delete from t1;
|
|
admin check table t1;
|
|
drop table if exists t1;
|
|
create table t1(c1 decimal(6,4));
|
|
insert into t1 set c1 = '1.1';
|
|
alter table t1 add index idx(c1);
|
|
delete from t1;
|
|
admin check table t1;
|
|
drop table if exists t1;
|
|
create table t1(c1 decimal);
|
|
insert into t1 set c1 = 1.1;
|
|
alter table t1 add index idx(c1);
|
|
delete from t1;
|
|
admin check table t1;
|
|
drop table if exists t1;
|
|
create table t1(c1 numeric);
|
|
insert into t1 set c1 = -1;
|
|
alter table t1 add index idx(c1);
|
|
delete from t1;
|
|
admin check table t1;
|
|
drop table if exists t1;
|
|
create table t1(c1 float);
|
|
insert into t1 set c1 = 1.2;
|
|
alter table t1 add index idx(c1);
|
|
delete from t1;
|
|
admin check table t1;
|
|
drop table if exists t1;
|
|
create table t1(c1 double);
|
|
insert into t1 set c1 = 1.2;
|
|
alter table t1 add index idx(c1);
|
|
delete from t1;
|
|
admin check table t1;
|
|
drop table if exists t1;
|
|
create table t1(c1 double);
|
|
insert into t1 set c1 = 1.3;
|
|
alter table t1 add index idx(c1);
|
|
delete from t1;
|
|
admin check table t1;
|
|
drop table if exists t1;
|
|
create table t1(c1 real);
|
|
insert into t1 set c1 = 1.4;
|
|
alter table t1 add index idx(c1);
|
|
delete from t1;
|
|
admin check table t1;
|
|
drop table if exists t1;
|
|
create table t1(c1 date);
|
|
insert into t1 set c1 = '2020-01-01';
|
|
alter table t1 add index idx(c1);
|
|
delete from t1;
|
|
admin check table t1;
|
|
drop table if exists t1;
|
|
create table t1(c1 time);
|
|
insert into t1 set c1 = '20:00:00';
|
|
alter table t1 add index idx(c1);
|
|
delete from t1;
|
|
admin check table t1;
|
|
drop table if exists t1;
|
|
create table t1(c1 datetime);
|
|
insert into t1 set c1 = '2020-01-01 22:22:22';
|
|
alter table t1 add index idx(c1);
|
|
delete from t1;
|
|
admin check table t1;
|
|
drop table if exists t1;
|
|
create table t1(c1 timestamp);
|
|
insert into t1 set c1 = '2020-01-01 22:22:22';
|
|
alter table t1 add index idx(c1);
|
|
delete from t1;
|
|
admin check table t1;
|
|
drop table if exists t1;
|
|
create table t1(c1 year);
|
|
insert into t1 set c1 = '2020';
|
|
alter table t1 add index idx(c1);
|
|
delete from t1;
|
|
admin check table t1;
|
|
drop table if exists t1;
|
|
create table t1(c1 char(15));
|
|
insert into t1 set c1 = 'test';
|
|
alter table t1 add index idx(c1);
|
|
delete from t1;
|
|
admin check table t1;
|
|
drop table if exists t1;
|
|
create table t1(c1 varchar(15));
|
|
insert into t1 set c1 = 'test';
|
|
alter table t1 add index idx(c1);
|
|
delete from t1;
|
|
admin check table t1;
|
|
drop table if exists t1;
|
|
create table t1(c1 binary(3));
|
|
insert into t1 set c1 = 'a';
|
|
alter table t1 add index idx(c1);
|
|
delete from t1;
|
|
admin check table t1;
|
|
drop table if exists t1;
|
|
create table t1(c1 varbinary(3));
|
|
insert into t1 set c1 = 'b';
|
|
alter table t1 add index idx(c1);
|
|
delete from t1;
|
|
admin check table t1;
|
|
drop table if exists t1;
|
|
create table t1(c1 blob);
|
|
insert into t1 set c1 = 'test';
|
|
alter table t1 add index idx(c1(3));
|
|
delete from t1;
|
|
admin check table t1;
|
|
drop table if exists t1;
|
|
create table t1(c1 text);
|
|
insert into t1 set c1 = 'test';
|
|
alter table t1 add index idx(c1(3));
|
|
delete from t1;
|
|
admin check table t1;
|
|
drop table if exists t1;
|
|
create table t1(c1 enum('a', 'b'));
|
|
insert into t1 set c1 = 'a';
|
|
alter table t1 add index idx(c1);
|
|
delete from t1;
|
|
admin check table t1;
|
|
drop table if exists t1;
|
|
create table t1(c1 set('a', 'b'));
|
|
insert into t1 set c1 = 'a,b';
|
|
alter table t1 add index idx(c1);
|
|
delete from t1;
|
|
admin check table t1;
|
|
drop table if exists c;
|
|
create table c(i int,j int,k int,primary key(i,j,k));
|
|
insert into c values(1,2,3);
|
|
insert into c values(1,2,4);
|
|
update c set i=1,j=2,k=4 where i=1 and j=2 and k=3;
|
|
Error 1062 (23000): Duplicate entry '1-2-4' for key 'c.PRIMARY'
|
|
drop table if exists t1, t2;
|
|
create table t1 (a int(11) ,b varchar(100) ,primary key (a));
|
|
create table t2 (c int(11) ,d varchar(100) ,primary key (c));
|
|
prepare in1 from 'insert into t1 (a,b) select c,null from t2 t on duplicate key update b=t.d';
|
|
execute in1;
|
|
drop table if exists t1;
|
|
create table t1(a bigint);
|
|
insert into t1 values("asfasdfsajhlkhlksdaf");
|
|
Error 1366 (HY000): Incorrect bigint value: 'asfasdfsajhlkhlksdaf' for column 'a' at row 1
|
|
drop table if exists t1;
|
|
create table t1(a varchar(10)) charset ascii;
|
|
insert into t1 values('我');
|
|
Error 1366 (HY000): Incorrect string value '\xE6\x88\x91' for column 'a'
|
|
drop table if exists t1;
|
|
create table t1(a char(10) charset utf8);
|
|
insert into t1 values('我');
|
|
alter table t1 add column b char(10) charset ascii as ((a));
|
|
select * from t1;
|
|
a b
|
|
我 ?
|
|
drop table if exists t;
|
|
create table t (a year);
|
|
insert into t values(2156);
|
|
Error 1264 (22003): Out of range value for column 'a' at row 1
|
|
DROP TABLE IF EXISTS ts;
|
|
CREATE TABLE ts (id int DEFAULT NULL, time1 TIMESTAMP NULL DEFAULT NULL);
|
|
SET @@sql_mode='';
|
|
INSERT INTO ts (id, time1) VALUES (1, TIMESTAMP '1018-12-23 00:00:00');
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '1018-12-23 00:00:00' for column 'time1' at row 1
|
|
SELECT * FROM ts ORDER BY id;
|
|
id time1
|
|
1 0000-00-00 00:00:00
|
|
SET @@sql_mode='STRICT_TRANS_TABLES';
|
|
INSERT INTO ts (id, time1) VALUES (2, TIMESTAMP '1018-12-24 00:00:00');
|
|
Error 1292 (22007): Incorrect timestamp value: '1018-12-24 00:00:00' for column 'time1' at row 1
|
|
DROP TABLE ts;
|
|
CREATE TABLE t0(c0 SMALLINT AUTO_INCREMENT PRIMARY KEY);
|
|
INSERT IGNORE INTO t0(c0) VALUES (194626268);
|
|
INSERT IGNORE INTO t0(c0) VALUES ('*');
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1366 Incorrect smallint value: '*' for column 'c0' at row 1
|
|
Warning 1690 constant 32768 overflows smallint
|
|
Warning 1467 Failed to read auto-increment value from storage engine
|
|
SET @@sql_mode=default;
|
|
drop table if exists t1;
|
|
create table t1(a decimal(15,2));
|
|
insert into t1 values (1111111111111.01);
|
|
select * from t1;
|
|
a
|
|
1111111111111.01
|
|
select cast(a as decimal) from t1;
|
|
cast(a as decimal)
|
|
9999999999
|
|
drop table if exists t1;
|
|
create table t1(a json, b int, unique index idx((cast(a as signed array))));
|
|
insert into t1 values ('[1,11]', 1);
|
|
insert into t1 values ('[2, 22]', 2);
|
|
select * from t1;
|
|
a b
|
|
[1, 11] 1
|
|
[2, 22] 2
|
|
insert into t1 values ('[2, 222]', 2);
|
|
Error 1062 (23000): Duplicate entry '2' for key 't1.idx'
|
|
replace into t1 values ('[1, 10]', 10);
|
|
select * from t1;
|
|
a b
|
|
[2, 22] 2
|
|
[1, 10] 10
|
|
replace into t1 values ('[1, 2]', 1);
|
|
select * from t1;
|
|
a b
|
|
[1, 2] 1
|
|
replace into t1 values ('[1, 11]', 1);
|
|
insert into t1 values ('[2, 22]', 2);
|
|
select * from t1;
|
|
a b
|
|
[1, 11] 1
|
|
[2, 22] 2
|
|
insert ignore into t1 values ('[1]', 2);
|
|
select * from t1;
|
|
a b
|
|
[1, 11] 1
|
|
[2, 22] 2
|
|
insert ignore into t1 values ('[1, 2]', 2);
|
|
select * from t1;
|
|
a b
|
|
[1, 11] 1
|
|
[2, 22] 2
|
|
insert into t1 values ('[2]', 2) on duplicate key update b = 10;
|
|
select * from t1;
|
|
a b
|
|
[1, 11] 1
|
|
[2, 22] 10
|
|
insert into t1 values ('[2, 1]', 2) on duplicate key update a = '[1,2]';
|
|
Error 1062 (23000): Duplicate entry '[1, 2]' for key 't1.idx'
|
|
insert into t1 values ('[1,2]', 2) on duplicate key update a = '[1,2]';
|
|
Error 1062 (23000): Duplicate entry '[1, 2]' for key 't1.idx'
|
|
insert into t1 values ('[11, 22]', 2) on duplicate key update a = '[1,2]';
|
|
Error 1062 (23000): Duplicate entry '[1, 2]' for key 't1.idx'
|
|
set time_zone="+09:00";
|
|
drop table if exists t;
|
|
create table t (id int, c1 datetime not null default CURRENT_TIMESTAMP);
|
|
set TIMESTAMP = 1234;
|
|
insert t (id) values (1);
|
|
select * from t;
|
|
id c1
|
|
1 1970-01-01 09:20:34
|
|
drop table if exists t;
|
|
create table t (dt datetime);
|
|
set @@time_zone='+08:00';
|
|
delete from t;
|
|
insert into t values ('2020-10-22');
|
|
select * from t;
|
|
dt
|
|
2020-10-22 00:00:00
|
|
delete from t;
|
|
insert into t values ('2020-10-22-16');
|
|
select * from t;
|
|
dt
|
|
2020-10-22 16:00:00
|
|
delete from t;
|
|
insert into t values ('2020-10-22 16-31');
|
|
select * from t;
|
|
dt
|
|
2020-10-22 16:31:00
|
|
delete from t;
|
|
insert into t values ('2020-10-22 16:31-15');
|
|
select * from t;
|
|
dt
|
|
2020-10-22 16:31:15
|
|
delete from t;
|
|
insert into t values ('2020-10-22T16:31:15-10');
|
|
select * from t;
|
|
dt
|
|
2020-10-23 10:31:15
|
|
delete from t;
|
|
insert into t values ('2020.10-22');
|
|
select * from t;
|
|
dt
|
|
2020-10-22 00:00:00
|
|
delete from t;
|
|
insert into t values ('2020-10.22-16');
|
|
select * from t;
|
|
dt
|
|
2020-10-22 16:00:00
|
|
delete from t;
|
|
insert into t values ('2020-10-22.16-31');
|
|
select * from t;
|
|
dt
|
|
2020-10-22 16:31:00
|
|
delete from t;
|
|
insert into t values ('2020-10-22 16.31-15');
|
|
select * from t;
|
|
dt
|
|
2020-10-22 16:31:15
|
|
delete from t;
|
|
insert into t values ('2020-10-22T16.31.15+14');
|
|
select * from t;
|
|
dt
|
|
2020-10-22 10:31:15
|
|
delete from t;
|
|
insert into t values ('2020-10:22');
|
|
select * from t;
|
|
dt
|
|
2020-10-22 00:00:00
|
|
delete from t;
|
|
insert into t values ('2020-10-22:16');
|
|
select * from t;
|
|
dt
|
|
2020-10-22 16:00:00
|
|
delete from t;
|
|
insert into t values ('2020-10-22-16:31');
|
|
select * from t;
|
|
dt
|
|
2020-10-22 16:31:00
|
|
delete from t;
|
|
insert into t values ('2020-10-22 16-31:15');
|
|
select * from t;
|
|
dt
|
|
2020-10-22 16:31:15
|
|
delete from t;
|
|
insert into t values ('2020-10-22T16.31.15+09:30');
|
|
select * from t;
|
|
dt
|
|
2020-10-22 15:01:15
|
|
delete from t;
|
|
insert into t values ('2020.10-22:16');
|
|
select * from t;
|
|
dt
|
|
2020-10-22 16:00:00
|
|
delete from t;
|
|
insert into t values ('2020-10.22-16:31');
|
|
select * from t;
|
|
dt
|
|
2020-10-22 16:31:00
|
|
delete from t;
|
|
insert into t values ('2020-10-22.16-31:15');
|
|
select * from t;
|
|
dt
|
|
2020-10-22 16:31:15
|
|
delete from t;
|
|
insert into t values ('2020-10-22T16:31.15+09:30');
|
|
select * from t;
|
|
dt
|
|
2020-10-22 15:01:15
|
|
drop table if exists t;
|
|
create table t (dt datetime, ts timestamp);
|
|
delete from t;
|
|
set @@time_zone='+08:00';
|
|
insert into t values ('2020-10-22T16:53:40Z', '2020-10-22T16:53:40Z');
|
|
set @@time_zone='+00:00';
|
|
select * from t;
|
|
dt ts
|
|
2020-10-23 00:53:40 2020-10-22 16:53:40
|
|
delete from t;
|
|
set @@time_zone='-08:00';
|
|
insert into t values ('2020-10-22T16:53:40Z', '2020-10-22T16:53:40Z');
|
|
set @@time_zone='+08:00';
|
|
select * from t;
|
|
dt ts
|
|
2020-10-22 08:53:40 2020-10-23 00:53:40
|
|
delete from t;
|
|
set @@time_zone='-03:00';
|
|
insert into t values ('2020-10-22T16:53:40+03:00', '2020-10-22T16:53:40+03:00');
|
|
set @@time_zone='+08:00';
|
|
select * from t;
|
|
dt ts
|
|
2020-10-22 10:53:40 2020-10-22 21:53:40
|
|
delete from t;
|
|
set @@time_zone='+08:00';
|
|
insert into t values ('2020-10-22T16:53:40+08:00', '2020-10-22T16:53:40+08:00');
|
|
set @@time_zone='+08:00';
|
|
select * from t;
|
|
dt ts
|
|
2020-10-22 16:53:40 2020-10-22 16:53:40
|
|
drop table if exists t;
|
|
create table t (ts timestamp);
|
|
insert into t values ('2020-10-22T12:00:00Z'), ('2020-10-22T13:00:00Z'), ('2020-10-22T14:00:00Z');
|
|
select count(*) from t where ts > '2020-10-22T12:00:00Z';
|
|
count(*)
|
|
2
|
|
set @@time_zone='+08:00';
|
|
drop table if exists t;
|
|
create table t (dt datetime(2), ts timestamp(2));
|
|
insert into t values ('2020-10-27T14:39:10.10+00:00', '2020-10-27T14:39:10.10+00:00');
|
|
select * from t;
|
|
dt ts
|
|
2020-10-27 22:39:10.10 2020-10-27 22:39:10.10
|
|
drop table if exists t;
|
|
create table t (dt datetime(1), ts timestamp(1));
|
|
insert into t values ('2020-10-27T14:39:10.3+0200', '2020-10-27T14:39:10.3+0200');
|
|
select * from t;
|
|
dt ts
|
|
2020-10-27 20:39:10.3 2020-10-27 20:39:10.3
|
|
drop table if exists t;
|
|
create table t (dt datetime(6), ts timestamp(6));
|
|
insert into t values ('2020-10-27T14:39:10.3-02', '2020-10-27T14:39:10.3-02');
|
|
select * from t;
|
|
dt ts
|
|
2020-10-28 00:39:10.300000 2020-10-28 00:39:10.300000
|
|
drop table if exists t;
|
|
create table t (dt datetime(2), ts timestamp(2));
|
|
insert into t values ('2020-10-27T14:39:10.10Z', '2020-10-27T14:39:10.10Z');
|
|
select * from t;
|
|
dt ts
|
|
2020-10-27 22:39:10.10 2020-10-27 22:39:10.10
|
|
set time_zone=default;
|
|
set timestamp=default;
|
|
drop table if exists t1;
|
|
create table t1(a year(4));
|
|
insert into t1 values(0000),(00),("0000"),("000"), ("00"), ("0"), (79), ("79");
|
|
select * from t1;
|
|
a
|
|
0000
|
|
0000
|
|
0000
|
|
2000
|
|
2000
|
|
2000
|
|
1979
|
|
1979
|
|
drop table if exists t;
|
|
create table t(f_year year NOT NULL DEFAULT '0000')ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
|
|
insert into t values();
|
|
select * from t;
|
|
f_year
|
|
0000
|
|
insert into t values('0000');
|
|
select * from t;
|
|
f_year
|
|
0000
|
|
0000
|
|
drop table if exists t1, t2, t3, t4;
|
|
create table t1(d date);
|
|
create table t2(d datetime);
|
|
create table t3(d date);
|
|
create table t4(d datetime);
|
|
set sql_mode='STRICT_TRANS_TABLES,ALLOW_INVALID_DATES';
|
|
insert into t1 values ('0000-00-00');
|
|
insert into t2 values ('0000-00-00');
|
|
insert into t1 values ('2019-00-00');
|
|
insert into t2 values ('2019-00-00');
|
|
insert into t1 values ('2019-01-00');
|
|
insert into t2 values ('2019-01-00');
|
|
insert into t1 values ('2019-00-01');
|
|
insert into t2 values ('2019-00-01');
|
|
insert into t1 values ('2019-02-31');
|
|
insert into t2 values ('2019-02-31');
|
|
select year(d), month(d), day(d) from t1;
|
|
year(d) month(d) day(d)
|
|
0 0 0
|
|
2019 0 0
|
|
2019 1 0
|
|
2019 0 1
|
|
2019 2 31
|
|
select year(d), month(d), day(d) from t2;
|
|
year(d) month(d) day(d)
|
|
0 0 0
|
|
2019 0 0
|
|
2019 1 0
|
|
2019 0 1
|
|
2019 2 31
|
|
insert t3 select d from t1;
|
|
select year(d), month(d), day(d) from t3;
|
|
year(d) month(d) day(d)
|
|
0 0 0
|
|
2019 0 0
|
|
2019 1 0
|
|
2019 0 1
|
|
2019 2 31
|
|
insert t4 select d from t2;
|
|
select year(d), month(d), day(d) from t4;
|
|
year(d) month(d) day(d)
|
|
0 0 0
|
|
2019 0 0
|
|
2019 1 0
|
|
2019 0 1
|
|
2019 2 31
|
|
truncate t1;truncate t2;truncate t3;truncate t4;
|
|
set sql_mode='ALLOW_INVALID_DATES';
|
|
insert into t1 values ('0000-00-00');
|
|
insert into t2 values ('0000-00-00');
|
|
insert into t1 values ('2019-00-00');
|
|
insert into t2 values ('2019-00-00');
|
|
insert into t1 values ('2019-01-00');
|
|
insert into t2 values ('2019-01-00');
|
|
insert into t1 values ('2019-00-01');
|
|
insert into t2 values ('2019-00-01');
|
|
insert into t1 values ('2019-02-31');
|
|
insert into t2 values ('2019-02-31');
|
|
select year(d), month(d), day(d) from t1;
|
|
year(d) month(d) day(d)
|
|
0 0 0
|
|
2019 0 0
|
|
2019 1 0
|
|
2019 0 1
|
|
2019 2 31
|
|
select year(d), month(d), day(d) from t2;
|
|
year(d) month(d) day(d)
|
|
0 0 0
|
|
2019 0 0
|
|
2019 1 0
|
|
2019 0 1
|
|
2019 2 31
|
|
insert t3 select d from t1;
|
|
select year(d), month(d), day(d) from t3;
|
|
year(d) month(d) day(d)
|
|
0 0 0
|
|
2019 0 0
|
|
2019 1 0
|
|
2019 0 1
|
|
2019 2 31
|
|
insert t4 select d from t2;
|
|
select year(d), month(d), day(d) from t4;
|
|
year(d) month(d) day(d)
|
|
0 0 0
|
|
2019 0 0
|
|
2019 1 0
|
|
2019 0 1
|
|
2019 2 31
|
|
set sql_mode=default;
|
|
drop table if exists t1, t2, t3;
|
|
create table t1 (a int,b int,primary key(a,b)) partition by range(a) (partition p0 values less than (100),partition p1 values less than (1000));
|
|
insert into t1 set a=1, b=1;
|
|
insert into t1 set a=1,b=1 on duplicate key update a=1,b=1;
|
|
select * from t1;
|
|
a b
|
|
1 1
|
|
create table t2 (a int,b int,primary key(a,b)) partition by hash(a) partitions 4;
|
|
insert into t2 set a=1,b=1;
|
|
insert into t2 set a=1,b=1 on duplicate key update a=1,b=1;
|
|
select * from t2;
|
|
a b
|
|
1 1
|
|
CREATE TABLE t3 (a int, b int, c int, d int, e int,
|
|
PRIMARY KEY (a,b),
|
|
UNIQUE KEY (b,c,d)
|
|
) PARTITION BY RANGE ( b ) (
|
|
PARTITION p0 VALUES LESS THAN (4),
|
|
PARTITION p1 VALUES LESS THAN (7),
|
|
PARTITION p2 VALUES LESS THAN (11)
|
|
);
|
|
insert into t3 values (1,2,3,4,5);
|
|
insert into t3 values (1,2,3,4,5),(6,2,3,4,6) on duplicate key update e = e + values(e);
|
|
select * from t3;
|
|
a b c d e
|
|
1 2 3 4 16
|
|
drop table if exists t1;
|
|
create table t1 (a bit(3));
|
|
insert into t1 values(-1);
|
|
Error 1406 (22001): Data too long for column 'a' at row 1
|
|
insert into t1 values(9);
|
|
Error 1406 (22001): Data too long for column 'a' at row 1
|
|
create table t64 (a bit(64));
|
|
insert into t64 values(-1);
|
|
insert into t64 values(18446744073709551615);
|
|
insert into t64 values(18446744073709551616);
|
|
Error 1264 (22003): Out of range value for column 'a' at row 1
|
|
drop table if exists bug;
|
|
create table bug (a varchar(100));
|
|
insert into bug select ifnull(JSON_UNQUOTE(JSON_EXTRACT('[{"amount":2000,"feeAmount":0,"merchantNo":"20190430140319679394","shareBizCode":"20160311162_SECOND"}]', '$[0].merchantNo')),'') merchant_no union SELECT '20180531557' merchant_no;
|
|
select * from bug;
|
|
a
|
|
20180531557
|
|
20190430140319679394
|
|
drop table if exists t;
|
|
create table t (a int, b double);
|
|
insert into t values (ifnull('',0)+0, 0);
|
|
insert into t values (0, ifnull('',0)+0);
|
|
select * from t;
|
|
a b
|
|
0 0
|
|
0 0
|
|
insert into t values ('', 0);
|
|
Error 1366 (HY000): Incorrect int value: '' for column 'a' at row 1
|
|
insert into t values (0, '');
|
|
Error 1366 (HY000): Incorrect double value: '' for column 'b' at row 1
|
|
update t set a = '';
|
|
Error 1292 (22007): Truncated incorrect DOUBLE value: ''
|
|
update t set b = '';
|
|
Error 1292 (22007): Truncated incorrect DOUBLE value: ''
|
|
update t set a = ifnull('',0)+0;
|
|
update t set b = ifnull('',0)+0;
|
|
delete from t where a = '';
|
|
select * from t;
|
|
a b
|
|
drop table if exists t,t1;
|
|
create table t(col1 FLOAT, col2 FLOAT(10,2), col3 DOUBLE, col4 DOUBLE(10,2), col5 DECIMAL, col6 DECIMAL(10,2));
|
|
insert into t values (-3.402823466E+68, -34028234.6611, -1.7976931348623157E+308, -17976921.34, -9999999999, -99999999.99);
|
|
Error 1264 (22003): Out of range value for column 'col1' at row 1
|
|
insert into t values (-34028234.6611, -3.402823466E+68, -1.7976931348623157E+308, -17976921.34, -9999999999, -99999999.99);
|
|
Error 1264 (22003): Out of range value for column 'col2' at row 1
|
|
create table t1(id1 float,id2 float);
|
|
insert ignore into t1 values(999999999999999999999999999999999999999,-999999999999999999999999999999999999999);
|
|
select @@warning_count;
|
|
@@warning_count
|
|
2
|
|
select convert(id1,decimal(65)),convert(id2,decimal(65)) from t1;
|
|
convert(id1,decimal(65)) convert(id2,decimal(65))
|
|
340282346638528860000000000000000000000 -340282346638528860000000000000000000000
|
|
set sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
|
|
drop table if exists t1;
|
|
CREATE TABLE t1(c1 TINYTEXT CHARACTER SET utf8mb4);
|
|
INSERT INTO t1 (c1) VALUES(REPEAT(X'C385', 128));
|
|
Error 1406 (22001): Data too long for column 'c1' at row 1
|
|
drop table if exists t1;
|
|
CREATE TABLE t1(c1 Text CHARACTER SET utf8mb4);
|
|
INSERT INTO t1 (c1) VALUES(REPEAT(X'C385', 32768));
|
|
Error 1406 (22001): Data too long for column 'c1' at row 1
|
|
drop table if exists t1;
|
|
CREATE TABLE t1(c1 mediumtext);
|
|
INSERT INTO t1 (c1) VALUES(REPEAT(X'C385', 8777215));
|
|
Error 1406 (22001): Data too long for column 'c1' at row 1
|
|
set sql_mode = 'ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
|
|
drop table if exists t1;
|
|
CREATE TABLE t1(c1 TINYTEXT CHARACTER SET utf8mb4);
|
|
INSERT INTO t1 (c1) VALUES(REPEAT(X'C385', 128));
|
|
select length(c1) from t1;
|
|
length(c1)
|
|
254
|
|
drop table if exists t1;
|
|
CREATE TABLE t1(c1 Text CHARACTER SET utf8mb4);
|
|
INSERT INTO t1 (c1) VALUES(REPEAT(X'C385', 32768));
|
|
select length(c1) from t1;
|
|
length(c1)
|
|
65534
|
|
set sql_mode = default;
|
|
set @@allow_auto_random_explicit_insert = true;
|
|
drop table if exists ar;
|
|
create table ar (id bigint key clustered auto_random, name char(10));
|
|
insert into ar(id) values (1);
|
|
select id from ar;
|
|
id
|
|
1
|
|
select last_insert_id();
|
|
last_insert_id()
|
|
0
|
|
delete from ar;
|
|
insert into ar(id) values (1), (2);
|
|
select id from ar;
|
|
id
|
|
1
|
|
2
|
|
select last_insert_id();
|
|
last_insert_id()
|
|
0
|
|
delete from ar;
|
|
drop table ar;
|
|
set @@allow_auto_random_explicit_insert = default;
|
|
drop table if exists t, t1;
|
|
create table t (a int primary key, b datetime, d date);
|
|
insert into t values (1, '2019-02-11 30:00:00', '2019-01-31');
|
|
Error 1292 (22007): Incorrect datetime value: '2019-02-11 30:00:00' for column 'b' at row 1
|
|
CREATE TABLE t1 (a BINARY(16) PRIMARY KEY);
|
|
INSERT INTO t1 VALUES (AES_ENCRYPT('a','a'));
|
|
INSERT INTO t1 VALUES (AES_ENCRYPT('a','a'));
|
|
Error 1062 (23000): Duplicate entry '{ W]\xA1\x06u\x9D\xBD\xB1\xA3.\xE2\xD9\xA7t' for key 't1.PRIMARY'
|
|
INSERT INTO t1 VALUES (AES_ENCRYPT('b','b'));
|
|
INSERT INTO t1 VALUES (AES_ENCRYPT('b','b'));
|
|
Error 1062 (23000): Duplicate entry '\x0C\x1E\x8DG`\xEB\x93 F&BC\xF0\xB5\xF4\xB7' for key 't1.PRIMARY'
|
|
drop table if exists t1;
|
|
create table t1 (a bit primary key) engine=innodb;
|
|
insert into t1 values (b'0');
|
|
insert into t1 values (b'0');
|
|
Error 1062 (23000): Duplicate entry '\x00' for key 't1.PRIMARY'
|
|
drop table t1;
|
|
create table t1(a binary(2) primary key);
|
|
insert into t1 values (0x7e7f);
|
|
insert into t1 values (0x7e7f);
|
|
Error 1062 (23000): Duplicate entry '~\x7F' for key 't1.PRIMARY'
|
|
drop table if exists t;
|
|
create table t(c numeric primary key);
|
|
insert ignore into t values(null);
|
|
insert into t values(0);
|
|
Error 1062 (23000): Duplicate entry '0' for key 't.PRIMARY'
|
|
set tidb_enable_clustered_index = on;
|
|
drop table if exists t1pk;
|
|
create table t1pk(id varchar(200) primary key, v int);
|
|
insert into t1pk(id, v) values('abc', 1);
|
|
select * from t1pk;
|
|
id v
|
|
abc 1
|
|
set @@tidb_constraint_check_in_place=true;
|
|
insert into t1pk(id, v) values('abc', 2);
|
|
Error 1062 (23000): Duplicate entry 'abc' for key 't1pk.PRIMARY'
|
|
set @@tidb_constraint_check_in_place=false;
|
|
insert into t1pk(id, v) values('abc', 3);
|
|
Error 1062 (23000): Duplicate entry 'abc' for key 't1pk.PRIMARY'
|
|
select v, id from t1pk;
|
|
v id
|
|
1 abc
|
|
select id from t1pk where id = 'abc';
|
|
id
|
|
abc
|
|
select v, id from t1pk where id = 'abc';
|
|
v id
|
|
1 abc
|
|
drop table if exists t3pk;
|
|
create table t3pk(id1 varchar(200), id2 varchar(200), v int, id3 int, primary key(id1, id2, id3));
|
|
insert into t3pk(id1, id2, id3, v) values('abc', 'xyz', 100, 1);
|
|
select * from t3pk;
|
|
id1 id2 v id3
|
|
abc xyz 1 100
|
|
set @@tidb_constraint_check_in_place=true;
|
|
insert into t3pk(id1, id2, id3, v) values('abc', 'xyz', 100, 2);
|
|
Error 1062 (23000): Duplicate entry 'abc-xyz-100' for key 't3pk.PRIMARY'
|
|
set @@tidb_constraint_check_in_place=false;
|
|
insert into t3pk(id1, id2, id3, v) values('abc', 'xyz', 100, 3);
|
|
Error 1062 (23000): Duplicate entry 'abc-xyz-100' for key 't3pk.PRIMARY'
|
|
select v, id3, id2, id1 from t3pk;
|
|
v id3 id2 id1
|
|
1 100 xyz abc
|
|
select id3, id2, id1 from t3pk where id3 = 100 and id2 = 'xyz' and id1 = 'abc';
|
|
id3 id2 id1
|
|
100 xyz abc
|
|
select id3, id2, id1, v from t3pk where id3 = 100 and id2 = 'xyz' and id1 = 'abc';
|
|
id3 id2 id1 v
|
|
100 xyz abc 1
|
|
insert into t3pk(id1, id2, id3, v) values('abc', 'xyz', 101, 1);
|
|
insert into t3pk(id1, id2, id3, v) values('abc', 'zzz', 101, 1);
|
|
drop table if exists t1pku;
|
|
create table t1pku(id varchar(200) primary key, uk int, v int, unique key ukk(uk));
|
|
insert into t1pku(id, uk, v) values('abc', 1, 2);
|
|
select * from t1pku where id = 'abc';
|
|
id uk v
|
|
abc 1 2
|
|
insert into t1pku(id, uk, v) values('aaa', 1, 3);
|
|
Error 1062 (23000): Duplicate entry '1' for key 't1pku.ukk'
|
|
select * from t1pku;
|
|
id uk v
|
|
abc 1 2
|
|
select * from t3pk where (id1, id2, id3) in (('abc', 'xyz', 100), ('abc', 'xyz', 101), ('abc', 'zzz', 101));
|
|
id1 id2 v id3
|
|
abc xyz 1 100
|
|
abc xyz 1 101
|
|
abc zzz 1 101
|
|
set @@tidb_constraint_check_in_place=default;
|
|
set tidb_enable_clustered_index = default;
|
|
set tidb_enable_clustered_index = on;
|
|
drop table if exists it1pk;
|
|
create table it1pk(id varchar(200) primary key, v int);
|
|
insert into it1pk(id, v) values('abc', 1);
|
|
insert ignore into it1pk(id, v) values('abc', 2);
|
|
select * from it1pk where id = 'abc';
|
|
id v
|
|
abc 1
|
|
drop table if exists it2pk;
|
|
create table it2pk(id1 varchar(200), id2 varchar(200), v int, primary key(id1, id2));
|
|
insert into it2pk(id1, id2, v) values('abc', 'cba', 1);
|
|
select * from it2pk where id1 = 'abc' and id2 = 'cba';
|
|
id1 id2 v
|
|
abc cba 1
|
|
insert ignore into it2pk(id1, id2, v) values('abc', 'cba', 2);
|
|
select * from it2pk where id1 = 'abc' and id2 = 'cba';
|
|
id1 id2 v
|
|
abc cba 1
|
|
drop table if exists it1pku;
|
|
create table it1pku(id varchar(200) primary key, uk int, v int, unique key ukk(uk));
|
|
insert into it1pku(id, uk, v) values('abc', 1, 2);
|
|
select * from it1pku where id = 'abc';
|
|
id uk v
|
|
abc 1 2
|
|
insert ignore into it1pku(id, uk, v) values('aaa', 1, 3), ('bbb', 2, 1);
|
|
select * from it1pku;
|
|
id uk v
|
|
abc 1 2
|
|
bbb 2 1
|
|
set tidb_enable_clustered_index = default;
|
|
set tidb_enable_clustered_index = on;
|
|
drop table if exists dt1pi;
|
|
create table dt1pi(id varchar(200) primary key, v int);
|
|
insert into dt1pi(id, v) values('abb', 1),('acc', 2);
|
|
insert into dt1pi(id, v) values('abb', 2) on duplicate key update v = v + 1;
|
|
select * from dt1pi;
|
|
id v
|
|
abb 2
|
|
acc 2
|
|
insert into dt1pi(id, v) values('abb', 2) on duplicate key update v = v + 1, id = 'xxx';
|
|
select * from dt1pi;
|
|
id v
|
|
acc 2
|
|
xxx 3
|
|
drop table if exists dt1piu;
|
|
create table dt1piu(id varchar(200) primary key, uk int, v int, unique key uuk(uk));
|
|
insert into dt1piu(id, uk, v) values('abb', 1, 10),('acc', 2, 20);
|
|
insert into dt1piu(id, uk, v) values('xyz', 1, 100) on duplicate key update v = v + 1;
|
|
select * from dt1piu;
|
|
id uk v
|
|
abb 1 11
|
|
acc 2 20
|
|
insert into dt1piu(id, uk, v) values('abb', 1, 2) on duplicate key update v = v + 1, id = 'xxx';
|
|
select * from dt1piu;
|
|
id uk v
|
|
acc 2 20
|
|
xxx 1 12
|
|
drop table if exists ts1pk;
|
|
create table ts1pk(id1 timestamp, id2 timestamp, v int, primary key(id1, id2));
|
|
insert into ts1pk (id1, id2, v) values('2018-01-01 11:11:11', '2018-01-01 11:11:11', 1);
|
|
select id1, id2, v from ts1pk;
|
|
id1 id2 v
|
|
2018-01-01 11:11:11 2018-01-01 11:11:11 1
|
|
insert into ts1pk (id1, id2, v) values('2018-01-01 11:11:11', '2018-01-01 11:11:11', 2) on duplicate key update v = values(v);
|
|
select id1, id2, v from ts1pk;
|
|
id1 id2 v
|
|
2018-01-01 11:11:11 2018-01-01 11:11:11 2
|
|
insert into ts1pk (id1, id2, v) values('2018-01-01 11:11:11', '2018-01-01 11:11:11', 2) on duplicate key update v = values(v), id1 = '2018-01-01 11:11:12';
|
|
select id1, id2, v from ts1pk;
|
|
id1 id2 v
|
|
2018-01-01 11:11:12 2018-01-01 11:11:11 2
|
|
set tidb_enable_clustered_index = default;
|
|
set tidb_enable_clustered_index = on;
|
|
drop table if exists pkt1;
|
|
CREATE TABLE pkt1 (a varchar(255), b int, index idx(b), primary key(a,b));
|
|
insert into pkt1 values ('aaa',1);
|
|
select b from pkt1 where b = 1;
|
|
b
|
|
1
|
|
drop table if exists pkt2;
|
|
CREATE TABLE pkt2 (a varchar(255), b int, unique index idx(b), primary key(a,b));
|
|
insert into pkt2 values ('aaa',1);
|
|
select b from pkt2 where b = 1;
|
|
b
|
|
1
|
|
drop table if exists issue_18232;
|
|
create table issue_18232 (a int, b int, c int, d int, primary key (a, b), index idx(c));
|
|
select a from issue_18232 use index (idx);
|
|
a
|
|
select b from issue_18232 use index (idx);
|
|
b
|
|
select a,b from issue_18232 use index (idx);
|
|
a b
|
|
select c from issue_18232 use index (idx);
|
|
c
|
|
select a,c from issue_18232 use index (idx);
|
|
a c
|
|
select b,c from issue_18232 use index (idx);
|
|
b c
|
|
select a,b,c from issue_18232 use index (idx);
|
|
a b c
|
|
select d from issue_18232 use index (idx);
|
|
d
|
|
select a,d from issue_18232 use index (idx);
|
|
a d
|
|
select b,d from issue_18232 use index (idx);
|
|
b d
|
|
select a,b,d from issue_18232 use index (idx);
|
|
a b d
|
|
select c,d from issue_18232 use index (idx);
|
|
c d
|
|
select a,c,d from issue_18232 use index (idx);
|
|
a c d
|
|
select b,c,d from issue_18232 use index (idx);
|
|
b c d
|
|
select a,b,c,d from issue_18232 use index (idx);
|
|
a b c d
|
|
set tidb_enable_clustered_index = default;
|
|
drop table if exists t1, t2;
|
|
create table t1(a year, primary key(a));
|
|
insert ignore into t1 values(null);
|
|
create table t2(a int, key(a));
|
|
insert into t2 values(0);
|
|
select /*+ hash_join(t1) */ * from t1 join t2 on t1.a = t2.a;
|
|
a a
|
|
0000 0
|
|
select /*+ inl_join(t1) */ * from t1 join t2 on t1.a = t2.a;
|
|
a a
|
|
0000 0
|
|
select /*+ inl_join(t2) */ * from t1 join t2 on t1.a = t2.a;
|
|
a a
|
|
0000 0
|
|
select /*+ inl_hash_join(t1) */ * from t1 join t2 on t1.a = t2.a;
|
|
a a
|
|
0000 0
|
|
select /*+ inl_merge_join(t1) */ * from t1 join t2 on t1.a = t2.a;
|
|
a a
|
|
0000 0
|
|
select /*+ merge_join(t1) */ * from t1 join t2 on t1.a = t2.a;
|
|
a a
|
|
0000 0
|
|
drop table if exists vctt;
|
|
create table vctt (v varchar(4), c char(4));
|
|
insert into vctt values ('ab ', 'ab ');
|
|
select * from vctt;
|
|
v c
|
|
ab ab
|
|
delete from vctt;
|
|
insert into vctt values ('ab\n\n\n', 'ab\n\n\n'), ('ab\t\t\t', 'ab\t\t\t'), ('ab ', 'ab '), ('ab\r\r\r', 'ab\r\r\r');
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1265 Data truncated for column 'v' at row 1
|
|
Warning 1265 Data truncated for column 'v' at row 2
|
|
Warning 1265 Data truncated for column 'v' at row 3
|
|
Warning 1265 Data truncated for column 'v' at row 4
|
|
select * from vctt;
|
|
v c
|
|
ab
|
|
|
|
ab
|
|
|
|
|
|
ab ab
|
|
ab ab
|
|
ab
|
|
|
|
ab
|
|
|
|
select length(v), length(c) from vctt;
|
|
length(v) length(c)
|
|
4 4
|
|
4 4
|
|
4 2
|
|
4 4
|
|
drop table if exists t1;
|
|
create table t1(a int, b varchar(20), primary key(a,b(3)) clustered);
|
|
insert into t1 values(1,'aaaaa');
|
|
insert into t1 values(1,'aaaaa');
|
|
Error 1062 (23000): Duplicate entry '1-aaa' for key 't1.PRIMARY'
|
|
insert into t1 select 1, 'aaa';
|
|
Error 1062 (23000): Duplicate entry '1-aaa' for key 't1.PRIMARY'
|
|
insert into t1 select 1, 'bb';
|
|
insert into t1 select 1, 'bb';
|
|
Error 1062 (23000): Duplicate entry '1-bb' for key 't1.PRIMARY'
|
|
drop table if exists bintest;
|
|
create table bintest (h enum(0x61, '1', 'b')) character set utf8mb4;
|
|
insert into bintest(h) values(0x61);
|
|
select * from bintest;
|
|
h
|
|
a
|
|
drop table if exists bintest;
|
|
create table bintest (h set(0x61, '1', 'b')) character set utf8mb4;
|
|
insert into bintest(h) values(0x61);
|
|
select * from bintest;
|
|
h
|
|
a
|
|
drop table if exists temp_test;
|
|
create global temporary table temp_test(id int primary key auto_increment) on commit delete rows;
|
|
insert into temp_test(id) values(0);
|
|
select * from temp_test;
|
|
id
|
|
begin;
|
|
insert into temp_test(id) values(0);
|
|
select * from temp_test;
|
|
id
|
|
1
|
|
commit;
|
|
begin;
|
|
insert into temp_test(id) values(0);
|
|
select * from temp_test;
|
|
id
|
|
1
|
|
insert into temp_test(id) values(0);
|
|
select id from temp_test order by id;
|
|
id
|
|
1
|
|
2
|
|
commit;
|
|
begin;
|
|
insert into temp_test(id) values(0), (0);
|
|
select id from temp_test order by id;
|
|
id
|
|
1
|
|
2
|
|
insert into temp_test(id) values(0), (0);
|
|
select id from temp_test order by id;
|
|
id
|
|
1
|
|
2
|
|
3
|
|
4
|
|
commit;
|
|
begin;
|
|
insert into temp_test(id) values(10);
|
|
insert into temp_test(id) values(0);
|
|
select id from temp_test order by id;
|
|
id
|
|
10
|
|
11
|
|
insert into temp_test(id) values(20), (30);
|
|
insert into temp_test(id) values(0), (0);
|
|
select id from temp_test order by id;
|
|
id
|
|
10
|
|
11
|
|
20
|
|
30
|
|
31
|
|
32
|
|
commit;
|
|
drop table if exists temp_test;
|
|
drop table if exists temp_test;
|
|
create global temporary table temp_test(id int) on commit delete rows;
|
|
insert into temp_test(id) values(0);
|
|
select _tidb_rowid from temp_test;
|
|
_tidb_rowid
|
|
begin;
|
|
insert into temp_test(id) values(0);
|
|
select _tidb_rowid from temp_test;
|
|
_tidb_rowid
|
|
1
|
|
commit;
|
|
begin;
|
|
insert into temp_test(id) values(0);
|
|
select _tidb_rowid from temp_test;
|
|
_tidb_rowid
|
|
1
|
|
insert into temp_test(id) values(0);
|
|
select _tidb_rowid from temp_test order by _tidb_rowid;
|
|
_tidb_rowid
|
|
1
|
|
2
|
|
commit;
|
|
begin;
|
|
insert into temp_test(id) values(0), (0);
|
|
select _tidb_rowid from temp_test order by _tidb_rowid;
|
|
_tidb_rowid
|
|
1
|
|
2
|
|
insert into temp_test(id) values(0), (0);
|
|
select _tidb_rowid from temp_test order by _tidb_rowid;
|
|
_tidb_rowid
|
|
1
|
|
2
|
|
3
|
|
4
|
|
commit;
|
|
drop table if exists temp_test;
|
|
drop table if exists t1;
|
|
create table t1(c1 date);
|
|
insert into t1 values('2020-02-31');
|
|
Error 1292 (22007): Incorrect date value: '2020-02-31' for column 'c1' at row 1
|
|
set @@sql_mode='ALLOW_INVALID_DATES';
|
|
insert into t1 values('2020-02-31');
|
|
select * from t1;
|
|
c1
|
|
2020-02-31
|
|
set @@sql_mode='STRICT_TRANS_TABLES';
|
|
insert into t1 values('2020-02-31');
|
|
Error 1292 (22007): Incorrect date value: '2020-02-31' for column 'c1' at row 1
|
|
set sql_mode=default;
|
|
drop table if exists t;
|
|
create table t (id decimal(10));
|
|
insert into t values('1sdf');
|
|
Error 1366 (HY000): Incorrect decimal value: '1sdf' for column 'id' at row 1
|
|
insert into t values('1edf');
|
|
Error 1366 (HY000): Incorrect decimal value: '1edf' for column 'id' at row 1
|
|
insert into t values('12Ea');
|
|
Error 1366 (HY000): Incorrect decimal value: '12Ea' for column 'id' at row 1
|
|
insert into t values('1E');
|
|
Error 1366 (HY000): Incorrect decimal value: '1E' for column 'id' at row 1
|
|
insert into t values('1e');
|
|
Error 1366 (HY000): Incorrect decimal value: '1e' for column 'id' at row 1
|
|
insert into t values('1.2A');
|
|
Error 1366 (HY000): Incorrect decimal value: '1.2A' for column 'id' at row 1
|
|
insert into t values('1.2.3.4.5');
|
|
Error 1366 (HY000): Incorrect decimal value: '1.2.3.4.5' for column 'id' at row 1
|
|
insert into t values('1.2.');
|
|
Error 1366 (HY000): Incorrect decimal value: '1.2.' for column 'id' at row 1
|
|
insert into t values('1,999.00');
|
|
Error 1366 (HY000): Incorrect decimal value: '1,999.00' for column 'id' at row 1
|
|
insert into t values('12e-3');
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1366 Incorrect decimal value: '12e-3' for column 'id' at row 1
|
|
select id from t;
|
|
id
|
|
0
|
|
drop table if exists t;
|
|
SET sql_mode='NO_ENGINE_SUBSTITUTION';
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE t1 (a tinyint not null auto_increment primary key, b char(20));
|
|
INSERT INTO t1 VALUES (127,'maxvalue');
|
|
REPLACE INTO t1 VALUES (0,'newmaxvalue');
|
|
Error 1467 (HY000): Failed to read auto-increment value from storage engine
|
|
set sql_mode=default;
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE t1(a INT) ENGINE = InnoDB;
|
|
INSERT IGNORE into t1(SELECT SLEEP(NULL));
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1210 Incorrect arguments to sleep
|
|
INSERT IGNORE into t1(SELECT SLEEP(-1));
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1210 Incorrect arguments to sleep
|
|
INSERT IGNORE into t1(SELECT SLEEP(1));
|
|
SELECT * FROM t1;
|
|
a
|
|
0
|
|
0
|
|
0
|
|
DROP TABLE t1;
|
|
drop table if exists t1;
|
|
create table t1(c1 float);
|
|
insert into t1 values(999.99);
|
|
select cast(t1.c1 as decimal(4, 1)) from t1;
|
|
cast(t1.c1 as decimal(4, 1))
|
|
999.9
|
|
select cast(t1.c1 as decimal(5, 1)) from t1;
|
|
cast(t1.c1 as decimal(5, 1))
|
|
1000.0
|
|
drop table if exists t1;
|
|
create table t1(c1 decimal(6, 4));
|
|
insert into t1 values(99.9999);
|
|
select cast(t1.c1 as decimal(5, 3)) from t1;
|
|
cast(t1.c1 as decimal(5, 3))
|
|
99.999
|
|
select cast(t1.c1 as decimal(6, 3)) from t1;
|
|
cast(t1.c1 as decimal(6, 3))
|
|
100.000
|
|
drop table if exists t1;
|
|
create table t1(id int, a int);
|
|
set @@SQL_MODE='STRICT_TRANS_TABLES';
|
|
insert into t1 values(1, '1e100');
|
|
Error 1264 (22003): Out of range value for column 'a' at row 1
|
|
insert into t1 values(2, '-1e100');
|
|
Error 1264 (22003): Out of range value for column 'a' at row 1
|
|
select id, a from t1;
|
|
id a
|
|
set @@SQL_MODE='';
|
|
insert into t1 values(1, '1e100');
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1264 Out of range value for column 'a' at row 1
|
|
insert into t1 values(2, '-1e100');
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1264 Out of range value for column 'a' at row 1
|
|
select id, a from t1 order by id asc;
|
|
id a
|
|
1 2147483647
|
|
2 -2147483648
|
|
set sql_mode=default;
|
|
drop table if exists tf;
|
|
create table tf(a float(1, 0) unsigned);
|
|
insert into tf values('-100');
|
|
Error 1264 (22003): Out of range value for column 'a' at row 1
|
|
set @@sql_mode='';
|
|
insert into tf values('-100');
|
|
select * from tf;
|
|
a
|
|
0
|
|
set @@sql_mode=default;
|
|
drop table if exists tt1;
|
|
create table tt1 (c1 decimal(64));
|
|
insert into tt1 values(89000000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
|
|
Error 1264 (22003): Out of range value for column 'c1' at row 1
|
|
insert into tt1 values(89123456789012345678901234567890123456789012345678901234567890123456789012345678900000000);
|
|
Error 1264 (22003): Out of range value for column 'c1' at row 1
|
|
insert ignore into tt1 values(89123456789012345678901234567890123456789012345678901234567890123456789012345678900000000);
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1264 Out of range value for column 'c1' at row 1
|
|
Warning 1292 Truncated incorrect DECIMAL value: '789012345678901234567890123456789012345678901234567890123456789012345678900000000'
|
|
select c1 from tt1;
|
|
c1
|
|
9999999999999999999999999999999999999999999999999999999999999999
|
|
update tt1 set c1 = 89123456789012345678901234567890123456789012345678901234567890123456789012345678900000000;
|
|
Error 1264 (22003): Out of range value for column 'c1' at row 1
|
|
drop table if exists tt1;
|
|
insert into tt1 values(4556414e723532);
|
|
Error 1367 (22007): Illegal double '4556414e723532' value found during parsing
|
|
select 888888888888888888888888888888888888888888888888888888888888888888888888888888888888;
|
|
888888888888888888888888888888888888888888888888888888888888888888888888888888888888
|
|
99999999999999999999999999999999999999999999999999999999999999999
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1292 Truncated incorrect DECIMAL value: '888888888888888888888888888888888888888888888888888888888888888888888888888888888'
|
|
drop table if exists t;
|
|
create table t (id smallint auto_increment primary key);
|
|
alter table t add column c1 int default 1;
|
|
insert ignore into t(id) values (194626268);
|
|
affected rows: 1
|
|
info:
|
|
select * from t;
|
|
id c1
|
|
32767 1
|
|
insert ignore into t(id) values ('*') on duplicate key update c1 = 2;
|
|
affected rows: 2
|
|
info:
|
|
select * from t;
|
|
id c1
|
|
32767 2
|
|
drop table if exists t;
|
|
create table t (i int not null primary key, j int unique key);
|
|
insert into t values (1, 1), (2, 2);
|
|
affected rows: 2
|
|
info: Records: 2 Duplicates: 0 Warnings: 0
|
|
insert ignore into t values(1, 1) on duplicate key update i = 2;
|
|
affected rows: 0
|
|
info:
|
|
select * from t;
|
|
i j
|
|
1 1
|
|
2 2
|
|
insert ignore into t values(1, 1) on duplicate key update j = 2;
|
|
affected rows: 0
|
|
info:
|
|
select * from t;
|
|
i j
|
|
1 1
|
|
2 2
|
|
drop table if exists t2;
|
|
create table t2(`col_25` set('Alice','Bob','Charlie','David') NOT NULL,`col_26` date NOT NULL DEFAULT '2016-04-15', PRIMARY KEY (`col_26`) clustered, UNIQUE KEY `idx_9` (`col_25`,`col_26`),UNIQUE KEY `idx_10` (`col_25`));
|
|
insert into t2(col_25, col_26) values('Bob', '1989-03-23'),('Alice', '2023-11-24'), ('Charlie', '2023-12-05');
|
|
insert ignore into t2 (col_25,col_26) values ( 'Bob','1977-11-23' ) on duplicate key update col_25 = 'Alice', col_26 = '2036-12-13';
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1062 Duplicate entry 'Alice' for key 't2.idx_10'
|
|
select * from t2;
|
|
col_25 col_26
|
|
Alice 2023-11-24
|
|
Bob 1989-03-23
|
|
Charlie 2023-12-05
|
|
drop table if exists t4;
|
|
create table t4(id int primary key clustered, k int, v int, unique key uk1(k));
|
|
insert into t4 values (1, 10, 100), (3, 30, 300);
|
|
insert ignore into t4 (id, k, v) values(1, 0, 0) on duplicate key update id = 2, k = 30;
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1062 Duplicate entry '30' for key 't4.uk1'
|
|
select * from t4;
|
|
id k v
|
|
1 10 100
|
|
3 30 300
|
|
drop table if exists t5;
|
|
create table t5(k1 varchar(100), k2 varchar(100), uk1 int, v int, primary key(k1, k2) clustered, unique key ukk1(uk1), unique key ukk2(v));
|
|
insert into t5(k1, k2, uk1, v) values('1', '1', 1, '100'), ('1', '3', 2, '200');
|
|
update ignore t5 set k2 = '2', uk1 = 2 where k1 = '1' and k2 = '1';
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1062 Duplicate entry '2' for key 't5.ukk1'
|
|
select * from t5;
|
|
k1 k2 uk1 v
|
|
1 1 1 100
|
|
1 3 2 200
|
|
drop table if exists t6;
|
|
create table t6 (a int, b int, c int, primary key(a, b) clustered, unique key idx_14(b), unique key idx_15(b), unique key idx_16(a, b));
|
|
insert into t6 select 10, 10, 20;
|
|
insert ignore into t6 set a = 20, b = 10 on duplicate key update a = 100;
|
|
select * from t6;
|
|
a b c
|
|
100 10 20
|
|
insert ignore into t6 set a = 200, b= 10 on duplicate key update c = 1000;
|
|
select * from t6;
|
|
a b c
|
|
100 10 1000
|
|
drop table if exists insert_autoinc_test;
|
|
create table insert_autoinc_test (id int primary key auto_increment, c1 int);
|
|
insert into insert_autoinc_test(c1) values (1), (2);
|
|
begin;
|
|
select * from insert_autoinc_test;
|
|
id c1
|
|
1 1
|
|
2 2
|
|
commit;
|
|
begin;
|
|
insert into insert_autoinc_test(id, c1) values (5,5);
|
|
insert into insert_autoinc_test(c1) values (6);
|
|
commit;
|
|
begin;
|
|
select * from insert_autoinc_test;
|
|
id c1
|
|
1 1
|
|
2 2
|
|
5 5
|
|
6 6
|
|
commit;
|
|
begin;
|
|
insert into insert_autoinc_test(id, c1) values (3,3);
|
|
commit;
|
|
begin;
|
|
select * from insert_autoinc_test;
|
|
id c1
|
|
1 1
|
|
2 2
|
|
3 3
|
|
5 5
|
|
6 6
|
|
commit;
|
|
begin;
|
|
insert into insert_autoinc_test(c1) values (7);
|
|
commit;
|
|
begin;
|
|
select * from insert_autoinc_test;
|
|
id c1
|
|
1 1
|
|
2 2
|
|
3 3
|
|
5 5
|
|
6 6
|
|
7 7
|
|
commit;
|
|
drop table if exists insert_autoinc_test;
|
|
create table insert_autoinc_test (id int primary key auto_increment, c1 int);
|
|
insert into insert_autoinc_test(id, c1) values (0.3, 1);
|
|
select * from insert_autoinc_test;
|
|
id c1
|
|
1 1
|
|
insert into insert_autoinc_test(id, c1) values (-0.3, 2);
|
|
select * from insert_autoinc_test;
|
|
id c1
|
|
1 1
|
|
2 2
|
|
insert into insert_autoinc_test(id, c1) values (-3.3, 3);
|
|
select * from insert_autoinc_test;
|
|
id c1
|
|
-3 3
|
|
1 1
|
|
2 2
|
|
insert into insert_autoinc_test(id, c1) values (4.3, 4);
|
|
select * from insert_autoinc_test;
|
|
id c1
|
|
-3 3
|
|
1 1
|
|
2 2
|
|
4 4
|
|
insert into insert_autoinc_test(c1) values (5);
|
|
select * from insert_autoinc_test;
|
|
id c1
|
|
-3 3
|
|
1 1
|
|
2 2
|
|
4 4
|
|
5 5
|
|
insert into insert_autoinc_test(id, c1) values (null, 6);
|
|
select * from insert_autoinc_test;
|
|
id c1
|
|
-3 3
|
|
1 1
|
|
2 2
|
|
4 4
|
|
5 5
|
|
6 6
|
|
drop table if exists insert_autoinc_test;
|
|
create table insert_autoinc_test (id int primary key auto_increment, c1 int);
|
|
insert into insert_autoinc_test(id, c1) values (5, 1);
|
|
select * from insert_autoinc_test;
|
|
id c1
|
|
5 1
|
|
insert into insert_autoinc_test(id, c1) values (0, 2);
|
|
select * from insert_autoinc_test;
|
|
id c1
|
|
5 1
|
|
6 2
|
|
insert into insert_autoinc_test(id, c1) values (0, 3);
|
|
select * from insert_autoinc_test;
|
|
id c1
|
|
5 1
|
|
6 2
|
|
7 3
|
|
set SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
|
|
insert into insert_autoinc_test(id, c1) values (0, 4);
|
|
select * from insert_autoinc_test;
|
|
id c1
|
|
0 4
|
|
5 1
|
|
6 2
|
|
7 3
|
|
insert into insert_autoinc_test(id, c1) values (0, 5);
|
|
Error 1062 (23000): Duplicate entry '0' for key 'insert_autoinc_test.PRIMARY'
|
|
insert into insert_autoinc_test(c1) values (6);
|
|
select * from insert_autoinc_test;
|
|
id c1
|
|
0 4
|
|
5 1
|
|
6 2
|
|
7 3
|
|
8 6
|
|
insert into insert_autoinc_test(id, c1) values (null, 7);
|
|
select * from insert_autoinc_test;
|
|
id c1
|
|
0 4
|
|
5 1
|
|
6 2
|
|
7 3
|
|
8 6
|
|
9 7
|
|
set SQL_MODE='';
|
|
insert into insert_autoinc_test(id, c1) values (0, 8);
|
|
select * from insert_autoinc_test;
|
|
id c1
|
|
0 4
|
|
5 1
|
|
6 2
|
|
7 3
|
|
8 6
|
|
9 7
|
|
10 8
|
|
insert into insert_autoinc_test(id, c1) values (null, 9);
|
|
select * from insert_autoinc_test;
|
|
id c1
|
|
0 4
|
|
5 1
|
|
6 2
|
|
7 3
|
|
8 6
|
|
9 7
|
|
10 8
|
|
11 9
|
|
set sql_mode = default;
|
|
drop table if exists insert_test;
|
|
create table insert_test (id int PRIMARY KEY AUTO_INCREMENT, c1 int, c2 int, c3 int default 1);
|
|
insert insert_test (c1) values (1),(2),(NULL);
|
|
affected rows: 3
|
|
info: Records: 3 Duplicates: 0 Warnings: 0
|
|
begin;
|
|
insert insert_test (c1) values ();
|
|
Error 1136 (21S01): Column count doesn't match value count at row 1
|
|
rollback;
|
|
begin;
|
|
insert insert_test (c1, c2) values (1,2),(1);
|
|
Error 1136 (21S01): Column count doesn't match value count at row 2
|
|
rollback;
|
|
begin;
|
|
insert insert_test (xxx) values (3);
|
|
Error 1054 (42S22): Unknown column 'xxx' in 'field list'
|
|
rollback;
|
|
begin;
|
|
insert insert_test_xxx (c1) values ();
|
|
Error 1146 (42S02): Table 'executor__insert.insert_test_xxx' doesn't exist
|
|
rollback;
|
|
insert insert_test set c1 = 3;
|
|
affected rows: 1
|
|
info:
|
|
begin;
|
|
insert insert_test set c1 = 4, c1 = 5;
|
|
Error 1110 (42000): Column 'c1' specified twice
|
|
rollback;
|
|
begin;
|
|
insert insert_test set xxx = 6;
|
|
Error 1054 (42S22): Unknown column 'xxx' in 'field list'
|
|
rollback;
|
|
drop table if exists insert_test_1, insert_test_2;
|
|
create table insert_test_1 (id int, c1 int);
|
|
insert insert_test_1 select id, c1 from insert_test;
|
|
affected rows: 4
|
|
info: Records: 4 Duplicates: 0 Warnings: 0
|
|
create table insert_test_2 (id int, c1 int);
|
|
insert insert_test_1 select id, c1 from insert_test union select id * 10, c1 * 10 from insert_test;
|
|
affected rows: 8
|
|
info: Records: 8 Duplicates: 0 Warnings: 0
|
|
begin;
|
|
insert insert_test_1 select c1 from insert_test;
|
|
Error 1136 (21S01): Column count doesn't match value count at row 1
|
|
rollback;
|
|
begin;
|
|
insert insert_test_1 values(default, default, default, default, default);
|
|
Error 1136 (21S01): Column count doesn't match value count at row 1
|
|
rollback;
|
|
select * from insert_test where id = 1;
|
|
id c1 c2 c3
|
|
1 1 NULL 1
|
|
insert into insert_test (id, c3) values (1, 2) on duplicate key update id=values(id), c2=10;
|
|
affected rows: 2
|
|
info:
|
|
select * from insert_test where id = 1;
|
|
id c1 c2 c3
|
|
1 1 10 1
|
|
insert into insert_test (id, c2) values (1, 1) on duplicate key update insert_test.c2=10;
|
|
affected rows: 0
|
|
info:
|
|
insert into insert_test (id, c2) values(1, 1) on duplicate key update t.c2 = 10;
|
|
Error 1054 (42S22): Unknown column 't.c2' in 'field list'
|
|
INSERT INTO insert_test (id, c3) VALUES (1, 2) ON DUPLICATE KEY UPDATE c3=values(c3)+c3+3;
|
|
affected rows: 2
|
|
info:
|
|
select * from insert_test where id = 1;
|
|
id c1 c2 c3
|
|
1 1 10 6
|
|
INSERT IGNORE INTO insert_test (id, c3) VALUES (1, 2) ON DUPLICATE KEY UPDATE c3=values(c3)+c3+3;
|
|
affected rows: 2
|
|
info:
|
|
select * from insert_test where id = 1;
|
|
id c1 c2 c3
|
|
1 1 10 11
|
|
drop table if exists insert_err;
|
|
create table insert_err (id int, c1 varchar(8));
|
|
insert insert_err values (1, 'abcdabcdabcd');
|
|
Error 1406 (22001): Data too long for column 'c1' at row 1
|
|
insert insert_err values (1, '你好，世界');
|
|
create table TEST1 (ID INT NOT NULL, VALUE INT DEFAULT NULL, PRIMARY KEY (ID));
|
|
INSERT INTO TEST1(id,value) VALUE(3,3) on DUPLICATE KEY UPDATE VALUE=4;
|
|
affected rows: 1
|
|
info:
|
|
drop table if exists t;
|
|
create table t (id int);
|
|
insert into t values(1);
|
|
update t t1 set id = (select count(*) + 1 from t t2 where t1.id = t2.id);
|
|
select * from t;
|
|
id
|
|
2
|
|
drop table if exists t;
|
|
create table t(c decimal(5, 5));
|
|
insert into t value(0);
|
|
insert into t value(1);
|
|
Error 1264 (22003): Out of range value for column 'c' at row 1
|
|
drop table if exists t;
|
|
create table t(c binary(255));
|
|
insert into t value(1);
|
|
select length(c) from t;
|
|
length(c)
|
|
255
|
|
drop table if exists t;
|
|
create table t(c varbinary(255));
|
|
insert into t value(1);
|
|
select length(c) from t;
|
|
length(c)
|
|
1
|
|
drop table if exists t;
|
|
create table t(c int);
|
|
set @@time_zone = '+08:00';
|
|
insert into t value(Unix_timestamp('2002-10-27 01:00'));
|
|
select * from t;
|
|
c
|
|
1035651600
|
|
set @@time_zone = default;
|
|
drop table if exists t1;
|
|
create table t1 (b char(0));
|
|
insert into t1 values ("");
|
|
DROP TABLE IF EXISTS t;
|
|
CREATE TABLE t(a DECIMAL(4,2));
|
|
INSERT INTO t VALUES (1.000001);
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1366 Incorrect decimal value: '1.000001' for column 'a' at row 1
|
|
INSERT INTO t VALUES (1.000000);
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
DROP TABLE IF EXISTS t;
|
|
CREATE TABLE t(a datetime);
|
|
INSERT INTO t VALUES('2017-00-00');
|
|
Error 1292 (22007): Incorrect datetime value: '2017-00-00' for column 'a' at row 1
|
|
set sql_mode = '';
|
|
INSERT INTO t VALUES('2017-00-00');
|
|
SELECT * FROM t;
|
|
a
|
|
2017-00-00 00:00:00
|
|
set sql_mode = 'strict_all_tables';
|
|
SELECT * FROM t;
|
|
a
|
|
2017-00-00 00:00:00
|
|
set sql_mode = default;
|
|
drop table if exists test;
|
|
CREATE TABLE test(id int(10) UNSIGNED NOT NULL AUTO_INCREMENT, p int(10) UNSIGNED NOT NULL, PRIMARY KEY(p), KEY(id));
|
|
insert into test(p) value(1);
|
|
select * from test;
|
|
id p
|
|
1 1
|
|
select * from test use index (id) where id = 1;
|
|
id p
|
|
1 1
|
|
insert into test values(NULL, 2);
|
|
select * from test use index (id) where id = 2;
|
|
id p
|
|
2 2
|
|
insert into test values(2, 3);
|
|
select * from test use index (id) where id = 2;
|
|
id p
|
|
2 2
|
|
2 3
|
|
drop table if exists t;
|
|
create table t(a bigint unsigned);
|
|
set @@sql_mode = 'strict_all_tables';
|
|
insert into t value (-1);
|
|
Error 1264 (22003): Out of range value for column 'a' at row 1
|
|
set @@sql_mode = '';
|
|
insert into t value (-1);
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1264 Out of range value for column 'a' at row 1
|
|
insert into t select -1;
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1690 constant -1 overflows bigint
|
|
insert into t select cast(-1 as unsigned);
|
|
insert into t value (-1.111);
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1264 Out of range value for column 'a' at row 1
|
|
insert into t value ('-1.111');
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1264 Out of range value for column 'a' at row 1
|
|
update t set a = -1 limit 1;
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1690 constant -1 overflows bigint
|
|
select * from t;
|
|
a
|
|
0
|
|
0
|
|
18446744073709551615
|
|
0
|
|
0
|
|
set @@sql_mode = default;
|
|
drop table if exists t;
|
|
create table t(a time(6));
|
|
insert into t value('20070219173709.055870'), ('20070219173709.055'), ('20070219173709.055870123');
|
|
select * from t;
|
|
a
|
|
17:37:09.055870
|
|
17:37:09.055000
|
|
17:37:09.055870
|
|
truncate table t;
|
|
insert into t value(20070219173709.055870), (20070219173709.055), (20070219173709.055870123);
|
|
select * from t;
|
|
a
|
|
17:37:09.055870
|
|
17:37:09.055000
|
|
17:37:09.055870
|
|
insert into t value(-20070219173709.055870);
|
|
Error 1292 (22007): Incorrect time value: '-20070219173709.055870' for column 'a' at row 1
|
|
drop table if exists t;
|
|
set @@sql_mode='';
|
|
create table t(a float unsigned, b double unsigned);
|
|
insert into t value(-1.1, -1.1), (-2.1, -2.1), (0, 0), (1.1, 1.1);
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1264 Out of range value for column 'a' at row 1
|
|
Warning 1264 Out of range value for column 'b' at row 1
|
|
Warning 1264 Out of range value for column 'a' at row 2
|
|
Warning 1264 Out of range value for column 'b' at row 2
|
|
select * from t;
|
|
a b
|
|
0 0
|
|
0 0
|
|
0 0
|
|
1.1 1.1
|
|
set @@sql_mode=default;
|
|
drop table if exists t;
|
|
create table t(a int default 1, b int default 2);
|
|
insert into t values(default, default);
|
|
select * from t;
|
|
a b
|
|
1 2
|
|
truncate table t;
|
|
insert into t values(default(b), default(a));
|
|
select * from t;
|
|
a b
|
|
2 1
|
|
truncate table t;
|
|
insert into t (b) values(default);
|
|
select * from t;
|
|
a b
|
|
1 2
|
|
truncate table t;
|
|
insert into t (b) values(default(a));
|
|
select * from t;
|
|
a b
|
|
1 1
|
|
drop view if exists v;
|
|
create view v as select * from t;
|
|
insert into v values(1,2);
|
|
Error 1105 (HY000): insert into view v is not supported now
|
|
replace into v values(1,2);
|
|
Error 1105 (HY000): replace into view v is not supported now
|
|
drop view v;
|
|
drop sequence if exists seq;
|
|
create sequence seq;
|
|
insert into seq values();
|
|
Error 1105 (HY000): insert into sequence seq is not supported now
|
|
replace into seq values();
|
|
Error 1105 (HY000): replace into sequence seq is not supported now
|
|
drop sequence seq;
|
|
drop table if exists t;
|
|
create table t(name varchar(255), b int, c int, primary key(name(2)));
|
|
insert into t(name, b) values("cha", 3);
|
|
insert into t(name, b) values("chb", 3);
|
|
Error 1062 (23000): Duplicate entry 'ch' for key 't.PRIMARY'
|
|
insert into t(name, b) values("测试", 3);
|
|
insert into t(name, b) values("测试", 3);
|
|
Error 1062 (23000): Duplicate entry '��' for key 't.PRIMARY'
|
|
drop table if exists t;
|
|
create table t (i int unique key);
|
|
insert into t values (1),(2);
|
|
affected rows: 2
|
|
info: Records: 2 Duplicates: 0 Warnings: 0
|
|
select * from t;
|
|
i
|
|
1
|
|
2
|
|
insert into t values (1), (2) on duplicate key update i = values(i);
|
|
affected rows: 0
|
|
info: Records: 2 Duplicates: 0 Warnings: 0
|
|
select * from t;
|
|
i
|
|
1
|
|
2
|
|
insert into t values (2), (3) on duplicate key update i = 3;
|
|
affected rows: 2
|
|
info: Records: 2 Duplicates: 1 Warnings: 0
|
|
select * from t;
|
|
i
|
|
1
|
|
3
|
|
drop table if exists t;
|
|
create table t (i int primary key, j int unique key);
|
|
insert into t values (-1, 1);
|
|
affected rows: 1
|
|
info:
|
|
select * from t;
|
|
i j
|
|
-1 1
|
|
insert into t values (1, 1) on duplicate key update j = values(j);
|
|
affected rows: 0
|
|
info:
|
|
select * from t;
|
|
i j
|
|
-1 1
|
|
drop table if exists test;
|
|
create table test (i int primary key, j int unique);
|
|
begin;
|
|
insert into test values (1,1);
|
|
insert into test values (2,1) on duplicate key update i = -i, j = -j;
|
|
commit;
|
|
select * from test;
|
|
i j
|
|
-1 -1
|
|
delete from test;
|
|
insert into test values (1, 1);
|
|
begin;
|
|
delete from test where i = 1;
|
|
insert into test values (2, 1) on duplicate key update i = -i, j = -j;
|
|
commit;
|
|
select * from test;
|
|
i j
|
|
2 1
|
|
delete from test;
|
|
insert into test values (1, 1);
|
|
begin;
|
|
update test set i = 2, j = 2 where i = 1;
|
|
insert into test values (1, 3) on duplicate key update i = -i, j = -j;
|
|
insert into test values (2, 4) on duplicate key update i = -i, j = -j;
|
|
commit;
|
|
select * from test order by i;
|
|
i j
|
|
-2 -2
|
|
1 3
|
|
delete from test;
|
|
begin;
|
|
insert into test values (1, 3), (1, 3) on duplicate key update i = values(i), j = values(j);
|
|
commit;
|
|
select * from test order by i;
|
|
i j
|
|
1 3
|
|
create table tmp (id int auto_increment, code int, primary key(id, code));
|
|
create table m (id int primary key auto_increment, code int unique);
|
|
insert tmp (code) values (1);
|
|
insert tmp (code) values (1);
|
|
set tidb_init_chunk_size=1;
|
|
insert m (code) select code from tmp on duplicate key update code = values(code);
|
|
select * from m;
|
|
id code
|
|
1 1
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE t1 (f1 INT AUTO_INCREMENT PRIMARY KEY,
|
|
f2 VARCHAR(5) NOT NULL UNIQUE);
|
|
INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UPDATE f1 = LAST_INSERT_ID(f1);
|
|
affected rows: 1
|
|
info:
|
|
SELECT LAST_INSERT_ID();
|
|
LAST_INSERT_ID()
|
|
1
|
|
INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UPDATE f1 = LAST_INSERT_ID(f1);
|
|
affected rows: 0
|
|
info:
|
|
SELECT LAST_INSERT_ID();
|
|
LAST_INSERT_ID()
|
|
1
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE t1 (f1 INT AUTO_INCREMENT UNIQUE,
|
|
f2 VARCHAR(5) NOT NULL UNIQUE);
|
|
INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UPDATE f1 = LAST_INSERT_ID(f1);
|
|
affected rows: 1
|
|
info:
|
|
SELECT LAST_INSERT_ID();
|
|
LAST_INSERT_ID()
|
|
1
|
|
INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UPDATE f1 = LAST_INSERT_ID(f1);
|
|
affected rows: 0
|
|
info:
|
|
SELECT LAST_INSERT_ID();
|
|
LAST_INSERT_ID()
|
|
1
|
|
INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UPDATE f1 = 2;
|
|
affected rows: 2
|
|
info:
|
|
SELECT LAST_INSERT_ID();
|
|
LAST_INSERT_ID()
|
|
1
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE t1 (f1 INT);
|
|
INSERT t1 VALUES (1) ON DUPLICATE KEY UPDATE f1 = 1;
|
|
affected rows: 1
|
|
info:
|
|
SELECT * FROM t1;
|
|
f1
|
|
1
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT NOT NULL UNIQUE);
|
|
INSERT t1 VALUES (1, 1);
|
|
affected rows: 1
|
|
info:
|
|
INSERT t1 VALUES (1, 1), (1, 1) ON DUPLICATE KEY UPDATE f1 = 2, f2 = 2;
|
|
affected rows: 3
|
|
info: Records: 2 Duplicates: 1 Warnings: 0
|
|
SELECT * FROM t1 order by f1;
|
|
f1 f2
|
|
1 1
|
|
2 2
|
|
INSERT t1 VALUES (1, 1) ON DUPLICATE KEY UPDATE f2 = null;
|
|
Error 1048 (23000): Column 'f2' cannot be null
|
|
INSERT IGNORE t1 VALUES (1, 1) ON DUPLICATE KEY UPDATE f2 = null;
|
|
affected rows: 2
|
|
info:
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1048 Column 'f2' cannot be null
|
|
SELECT * FROM t1 order by f1;
|
|
f1 f2
|
|
1 0
|
|
2 2
|
|
SET sql_mode='';
|
|
INSERT t1 VALUES (1, 1) ON DUPLICATE KEY UPDATE f2 = null;
|
|
Error 1048 (23000): Column 'f2' cannot be null
|
|
SELECT * FROM t1 order by f1;
|
|
f1 f2
|
|
1 0
|
|
2 2
|
|
set sql_mode=default;
|
|
set tidb_init_chunk_size=default;
|
|
drop table if exists t1, t2;
|
|
create table t1(a1 bigint primary key, b1 bigint);
|
|
create table t2(a2 bigint primary key, b2 bigint);
|
|
insert into t1 values(1, 100);
|
|
affected rows: 1
|
|
info:
|
|
insert into t2 values(1, 200);
|
|
affected rows: 1
|
|
info:
|
|
insert into t1 select a2, b2 from t2 on duplicate key update b1 = a2;
|
|
affected rows: 2
|
|
info: Records: 1 Duplicates: 1 Warnings: 0
|
|
select * from t1;
|
|
a1 b1
|
|
1 1
|
|
insert into t1 select a2, b2 from t2 on duplicate key update b1 = b2;
|
|
affected rows: 2
|
|
info: Records: 1 Duplicates: 1 Warnings: 0
|
|
select * from t1;
|
|
a1 b1
|
|
1 200
|
|
insert into t1 select a2, b2 from t2 on duplicate key update a1 = a2;
|
|
affected rows: 0
|
|
info: Records: 1 Duplicates: 0 Warnings: 0
|
|
select * from t1;
|
|
a1 b1
|
|
1 200
|
|
insert into t1 select a2, b2 from t2 on duplicate key update b1 = 300;
|
|
affected rows: 2
|
|
info: Records: 1 Duplicates: 1 Warnings: 0
|
|
select * from t1;
|
|
a1 b1
|
|
1 300
|
|
insert into t1 values(1, 1) on duplicate key update b1 = 400;
|
|
affected rows: 2
|
|
info:
|
|
select * from t1;
|
|
a1 b1
|
|
1 400
|
|
insert into t1 select 1, 500 from t2 on duplicate key update b1 = 400;
|
|
affected rows: 0
|
|
info: Records: 1 Duplicates: 0 Warnings: 0
|
|
select * from t1;
|
|
a1 b1
|
|
1 400
|
|
drop table if exists t1, t2;
|
|
create table t1(a bigint primary key, b bigint);
|
|
create table t2(a bigint primary key, b bigint);
|
|
insert into t1 select * from t2 on duplicate key update c = t2.b;
|
|
Error 1054 (42S22): Unknown column 'c' in 'field list'
|
|
drop table if exists t1, t2;
|
|
create table t1(a bigint primary key, b bigint);
|
|
create table t2(a bigint primary key, b bigint);
|
|
insert into t1 select * from t2 on duplicate key update a = b;
|
|
Error 1052 (23000): Column 'b' in field list is ambiguous
|
|
drop table if exists t1, t2;
|
|
create table t1(a bigint primary key, b bigint);
|
|
create table t2(a bigint primary key, b bigint);
|
|
insert into t1 select * from t2 on duplicate key update c = b;
|
|
Error 1054 (42S22): Unknown column 'c' in 'field list'
|
|
drop table if exists t1, t2;
|
|
create table t1(a1 bigint primary key, b1 bigint);
|
|
create table t2(a2 bigint primary key, b2 bigint);
|
|
insert into t1 select * from t2 on duplicate key update a1 = values(b2);
|
|
Error 1054 (42S22): Unknown column 'b2' in 'field list'
|
|
drop table if exists t1, t2;
|
|
create table t1(a1 bigint primary key, b1 bigint);
|
|
create table t2(a2 bigint primary key, b2 bigint);
|
|
insert into t1 values(1, 100);
|
|
affected rows: 1
|
|
info:
|
|
insert into t2 values(1, 200);
|
|
affected rows: 1
|
|
info:
|
|
insert into t1 select * from t2 on duplicate key update b1 = values(b1) + b2;
|
|
affected rows: 2
|
|
info: Records: 1 Duplicates: 1 Warnings: 0
|
|
select * from t1;
|
|
a1 b1
|
|
1 400
|
|
insert into t1 select * from t2 on duplicate key update b1 = values(b1) + b2;
|
|
affected rows: 0
|
|
info: Records: 1 Duplicates: 0 Warnings: 0
|
|
select * from t1;
|
|
a1 b1
|
|
1 400
|
|
drop table if exists t;
|
|
create table t(k1 bigint, k2 bigint, val bigint, primary key(k1, k2));
|
|
insert into t (val, k1, k2) values (3, 1, 2);
|
|
affected rows: 1
|
|
info:
|
|
select * from t;
|
|
k1 k2 val
|
|
1 2 3
|
|
insert into t (val, k1, k2) select c, a, b from (select 1 as a, 2 as b, 4 as c) tmp on duplicate key update val = tmp.c;
|
|
affected rows: 2
|
|
info: Records: 1 Duplicates: 1 Warnings: 0
|
|
select * from t;
|
|
k1 k2 val
|
|
1 2 4
|
|
drop table if exists t;
|
|
create table t(k1 double, k2 double, v double, primary key(k1, k2));
|
|
insert into t (v, k1, k2) select c, a, b from (select "3" c, "1" a, "2" b) tmp on duplicate key update v=c;
|
|
affected rows: 1
|
|
info: Records: 1 Duplicates: 0 Warnings: 0
|
|
select * from t;
|
|
k1 k2 v
|
|
1 2 3
|
|
insert into t (v, k1, k2) select c, a, b from (select "3" c, "1" a, "2" b) tmp on duplicate key update v=c;
|
|
affected rows: 0
|
|
info: Records: 1 Duplicates: 0 Warnings: 0
|
|
select * from t;
|
|
k1 k2 v
|
|
1 2 3
|
|
drop table if exists t1, t2;
|
|
create table t1(id int, a int, b int);
|
|
insert into t1 values (1, 1, 1);
|
|
affected rows: 1
|
|
info:
|
|
insert into t1 values (2, 2, 1);
|
|
affected rows: 1
|
|
info:
|
|
insert into t1 values (3, 3, 1);
|
|
affected rows: 1
|
|
info:
|
|
create table t2(a int primary key, b int, unique(b));
|
|
insert into t2 select a, b from t1 order by id on duplicate key update a=t1.a, b=t1.b;
|
|
affected rows: 5
|
|
info: Records: 3 Duplicates: 2 Warnings: 0
|
|
select * from t2 order by a;
|
|
a b
|
|
3 1
|
|
drop table if exists t1, t2;
|
|
create table t1(id int, a int, b int);
|
|
insert into t1 values (1, 1, 1);
|
|
affected rows: 1
|
|
info:
|
|
insert into t1 values (2, 1, 2);
|
|
affected rows: 1
|
|
info:
|
|
insert into t1 values (3, 3, 1);
|
|
affected rows: 1
|
|
info:
|
|
create table t2(a int primary key, b int, unique(b));
|
|
insert into t2 select a, b from t1 order by id on duplicate key update a=t1.a, b=t1.b;
|
|
affected rows: 4
|
|
info: Records: 3 Duplicates: 1 Warnings: 0
|
|
select * from t2 order by a;
|
|
a b
|
|
1 2
|
|
3 1
|
|
drop table if exists t1, t2;
|
|
create table t1(id int, a int, b int, c int);
|
|
insert into t1 values (1, 1, 1, 1);
|
|
affected rows: 1
|
|
info:
|
|
insert into t1 values (2, 2, 1, 2);
|
|
affected rows: 1
|
|
info:
|
|
insert into t1 values (3, 3, 2, 2);
|
|
affected rows: 1
|
|
info:
|
|
insert into t1 values (4, 4, 2, 2);
|
|
affected rows: 1
|
|
info:
|
|
create table t2(a int primary key, b int, c int, unique(b), unique(c));
|
|
insert into t2 select a, b, c from t1 order by id on duplicate key update b=t2.b, c=t2.c;
|
|
affected rows: 2
|
|
info: Records: 4 Duplicates: 0 Warnings: 0
|
|
select * from t2 order by a;
|
|
a b c
|
|
1 1 1
|
|
3 2 2
|
|
drop table if exists t1;
|
|
create table t1(a int primary key, b int);
|
|
insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5);
|
|
affected rows: 5
|
|
info: Records: 5 Duplicates: 0 Warnings: 0
|
|
insert into t1 values(4,14),(5,15),(6,16),(7,17),(8,18) on duplicate key update b=b+10;
|
|
affected rows: 7
|
|
info: Records: 5 Duplicates: 2 Warnings: 0
|
|
drop table if exists a, b;
|
|
create table a(x int primary key);
|
|
create table b(x int, y int);
|
|
insert into a values(1);
|
|
affected rows: 1
|
|
info:
|
|
insert into b values(1, 2);
|
|
affected rows: 1
|
|
info:
|
|
insert into a select x from b ON DUPLICATE KEY UPDATE a.x=b.y;
|
|
affected rows: 2
|
|
info: Records: 1 Duplicates: 1 Warnings: 0
|
|
select * from a;
|
|
x
|
|
2
|
|
## Test issue 28078.
|
|
## Use different types of columns so that there's likely to be error if the types mismatches.
|
|
drop table if exists a, b;
|
|
create table a(id int, a1 timestamp, a2 varchar(10), a3 float, unique(id));
|
|
create table b(id int, b1 time, b2 varchar(10), b3 int);
|
|
insert into a values (1, '2022-01-04 07:02:04', 'a', 1.1), (2, '2022-01-04 07:02:05', 'b', 2.2);
|
|
affected rows: 2
|
|
info: Records: 2 Duplicates: 0 Warnings: 0
|
|
insert into b values (2, '12:34:56', 'c', 10), (3, '01:23:45', 'd', 20);
|
|
affected rows: 2
|
|
info: Records: 2 Duplicates: 0 Warnings: 0
|
|
insert into a (id) select id from b on duplicate key update a.a2 = b.b2, a.a3 = 3.3;
|
|
affected rows: 3
|
|
info: Records: 2 Duplicates: 1 Warnings: 0
|
|
select * from a;
|
|
id a1 a2 a3
|
|
1 2022-01-04 07:02:04 a 1.1
|
|
2 2022-01-04 07:02:05 c 3.3
|
|
3 NULL NULL NULL
|
|
insert into a (id) select 4 from b where b3 = 20 on duplicate key update a.a3 = b.b3;
|
|
affected rows: 1
|
|
info: Records: 1 Duplicates: 0 Warnings: 0
|
|
select * from a;
|
|
id a1 a2 a3
|
|
1 2022-01-04 07:02:04 a 1.1
|
|
2 2022-01-04 07:02:05 c 3.3
|
|
3 NULL NULL NULL
|
|
4 NULL NULL NULL
|
|
insert into a (a2, a3) select 'x', 1.2 from b on duplicate key update a.a2 = b.b3;
|
|
affected rows: 2
|
|
info: Records: 2 Duplicates: 0 Warnings: 0
|
|
select * from a;
|
|
id a1 a2 a3
|
|
1 2022-01-04 07:02:04 a 1.1
|
|
2 2022-01-04 07:02:05 c 3.3
|
|
3 NULL NULL NULL
|
|
4 NULL NULL NULL
|
|
NULL NULL x 1.2
|
|
NULL NULL x 1.2
|
|
## reproduce insert on duplicate key update bug under new row format.
|
|
drop table if exists t1;
|
|
create table t1(c1 decimal(6,4), primary key(c1));
|
|
insert into t1 set c1 = 0.1;
|
|
insert into t1 set c1 = 0.1 on duplicate key update c1 = 1;
|
|
select * from t1 use index(primary);
|
|
c1
|
|
1.0000
|
|
drop table if exists t;
|
|
create table t (d int);
|
|
insert into t values (cast('18446744073709551616' as unsigned));
|
|
Error 1690 (22003): BIGINT UNSIGNED value is out of range in '18446744073709551616'
|
|
set sql_mode='';
|
|
insert into t values (cast('18446744073709551616' as unsigned));
|
|
Level Code Message
|
|
Warning 1264 Out of range value for column 'd' at row 1
|
|
Warning 1292 Truncated incorrect INTEGER value: '18446744073709551616'
|
|
set sql_mode=DEFAULT;
|
|
drop table if exists parent, child;
|
|
create table parent (id int primary key, ref int, key(ref));
|
|
create table child (id int primary key, ref int, foreign key (ref) references parent(ref));
|
|
insert into parent values (1, 1), (2, 2);
|
|
insert into child values (1, 1);
|
|
insert into child values (1, 2) on duplicate key update ref = 2;
|
|
insert into child values (1, 3) on duplicate key update ref = 3;
|
|
Error 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`executor__insert`.`child`, CONSTRAINT `fk_1` FOREIGN KEY (`ref`) REFERENCES `parent` (`ref`))
|
|
insert ignore into child values (1, 3) on duplicate key update ref = 3;
|
|
Level Code Message
|
|
Warning 1452 Cannot add or update a child row: a foreign key constraint fails (`executor__insert`.`child`, CONSTRAINT `fk_1` FOREIGN KEY (`ref`) REFERENCES `parent` (`ref`))
|
|
insert into parent values (2, 3) on duplicate key update ref = 3;
|
|
Error 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`executor__insert`.`child`, CONSTRAINT `fk_1` FOREIGN KEY (`ref`) REFERENCES `parent` (`ref`))
|
|
insert ignore into parent values (2, 3) on duplicate key update ref = 3;
|
|
drop table if exists t1, t2;
|
|
create table t1 (id int primary key, col1 varchar(10) not null default '');
|
|
create table t2 (id int primary key, col1 varchar(10));
|
|
insert into t2 values (1, null);
|
|
insert ignore into t1 values(5, null);
|
|
set session sql_mode = '';
|
|
insert into t1 values(1, null);
|
|
Error 1048 (23000): Column 'col1' cannot be null
|
|
insert into t1 set id = 1, col1 = null;
|
|
Error 1048 (23000): Column 'col1' cannot be null
|
|
insert t1 VALUES (5, 5) ON DUPLICATE KEY UPDATE col1 = null;
|
|
Error 1048 (23000): Column 'col1' cannot be null
|
|
insert t1 VALUES (5, 5), (6, null) ON DUPLICATE KEY UPDATE col1 = null;
|
|
select * from t1;
|
|
id col1
|
|
5
|
|
6
|
|
insert into t1 select * from t2;
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1048 Column 'col1' cannot be null
|
|
insert into t1 values(2, null), (3, 3), (4, 4);
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1048 Column 'col1' cannot be null
|
|
update t1 set col1 = null where id = 3;
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1048 Column 'col1' cannot be null
|
|
insert ignore t1 VALUES (4, 4) ON DUPLICATE KEY UPDATE col1 = null;
|
|
select * from t1;
|
|
id col1
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
6
|
|
drop table if exists t1;
|
|
create table t1 (id binary(20) unique);
|
|
INSERT IGNORE INTO t1 VALUES (X'0e6b4234fd0b08d4c4ec656529d94df02b37c472');
|
|
INSERT IGNORE INTO t1 VALUES (X'0e6b4234fd0b08d4c4ec656529d94df02b37c472');
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1062 Duplicate entry '\x0EkB4\xFD\x0B\x08\xD4\xC4\xECee)\xD9M\xF0+7\xC4r' for key 't1.id'
|
|
INSERT IGNORE INTO t1 VALUES (X'');
|
|
INSERT IGNORE INTO t1 VALUES (X'');
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1062 Duplicate entry '\x00' for key 't1.id'
|
|
INSERT IGNORE INTO t1 VALUES (X'7f000000');
|
|
INSERT IGNORE INTO t1 VALUES (X'7f000000');
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1062 Duplicate entry '\x7F' for key 't1.id'
|
|
drop table if exists t1;
|
|
create table t1 (id bit(20) unique);
|
|
INSERT IGNORE INTO t1 VALUES (10);
|
|
INSERT IGNORE INTO t1 VALUES (10);
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1062 Duplicate entry '\x00\x00\x0A' for key 't1.id'
|
|
INSERT IGNORE INTO t1 VALUES (65536);
|
|
INSERT IGNORE INTO t1 VALUES (65536);
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1062 Duplicate entry '\x01\x00\x00' for key 't1.id'
|
|
INSERT IGNORE INTO t1 VALUES (35);
|
|
INSERT IGNORE INTO t1 VALUES (35);
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1062 Duplicate entry '\x00\x00#' for key 't1.id'
|
|
INSERT IGNORE INTO t1 VALUES (127);
|
|
INSERT IGNORE INTO t1 VALUES (127);
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1062 Duplicate entry '\x00\x00\x7F' for key 't1.id'
|
|
SET @old_time_zone = @@time_zone;
|
|
SET @@time_zone = 'Europe/Amsterdam';
|
|
SET @old_sql_mode = @@sql_mode;
|
|
DROP TABLE IF EXISTS t;
|
|
CREATE TABLE t (id int primary key, ts timestamp, mode varchar(255));
|
|
SET SQL_MODE = '';
|
|
INSERT INTO t VALUES (1, '0000-00-00 00:00:00', '');
|
|
INSERT INTO t VALUES (2, '2025-00-30 00:00:00', '');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-00-30 00:00:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (3, '2025-02-30 00:00:00', '');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-02-30 00:00:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (4, '2025-03-30 01:59:59', '');
|
|
INSERT INTO t VALUES (5, '2025-03-30 02:00:00', '');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-03-30 02:00:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (6, '2025-03-30 02:30:00', '');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-03-30 02:30:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (7, '2025-03-30 03:00:00', '');
|
|
SET SQL_MODE = 'ALLOW_INVALID_DATES';
|
|
INSERT INTO t VALUES (8, '0000-00-00 00:00:00', 'ALLOW_INVALID_DATES');
|
|
INSERT INTO t VALUES (9, '2025-00-30 00:00:00', 'ALLOW_INVALID_DATES');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-00-30 00:00:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (10, '2025-02-30 00:00:00', 'ALLOW_INVALID_DATES');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-02-30 00:00:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (11, '2025-03-30 01:59:59', 'ALLOW_INVALID_DATES');
|
|
INSERT INTO t VALUES (12, '2025-03-30 02:00:00', 'ALLOW_INVALID_DATES');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-03-30 02:00:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (13, '2025-03-30 02:30:00', 'ALLOW_INVALID_DATES');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-03-30 02:30:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (14, '2025-03-30 03:00:00', 'ALLOW_INVALID_DATES');
|
|
SET SQL_MODE = 'NO_ZERO_IN_DATE';
|
|
INSERT INTO t VALUES (15, '0000-00-00 00:00:00', 'NO_ZERO_IN_DATE');
|
|
INSERT INTO t VALUES (16, '2025-00-30 00:00:00', 'NO_ZERO_IN_DATE');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-00-30 00:00:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (17, '2025-02-30 00:00:00', 'NO_ZERO_IN_DATE');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-02-30 00:00:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (18, '2025-03-30 01:59:59', 'NO_ZERO_IN_DATE');
|
|
INSERT INTO t VALUES (19, '2025-03-30 02:00:00', 'NO_ZERO_IN_DATE');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-03-30 02:00:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (20, '2025-03-30 02:30:00', 'NO_ZERO_IN_DATE');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-03-30 02:30:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (21, '2025-03-30 03:00:00', 'NO_ZERO_IN_DATE');
|
|
SET SQL_MODE = 'NO_ZERO_IN_DATE,ALLOW_INVALID_DATES';
|
|
INSERT INTO t VALUES (22, '0000-00-00 00:00:00', 'NO_ZERO_IN_DATE,ALLOW_INVALID_DATES');
|
|
INSERT INTO t VALUES (23, '2025-00-30 00:00:00', 'NO_ZERO_IN_DATE,ALLOW_INVALID_DATES');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-00-30 00:00:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (24, '2025-02-30 00:00:00', 'NO_ZERO_IN_DATE,ALLOW_INVALID_DATES');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-02-30 00:00:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (25, '2025-03-30 01:59:59', 'NO_ZERO_IN_DATE,ALLOW_INVALID_DATES');
|
|
INSERT INTO t VALUES (26, '2025-03-30 02:00:00', 'NO_ZERO_IN_DATE,ALLOW_INVALID_DATES');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-03-30 02:00:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (27, '2025-03-30 02:30:00', 'NO_ZERO_IN_DATE,ALLOW_INVALID_DATES');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-03-30 02:30:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (28, '2025-03-30 03:00:00', 'NO_ZERO_IN_DATE,ALLOW_INVALID_DATES');
|
|
SET SQL_MODE = 'NO_ZERO_DATE';
|
|
INSERT INTO t VALUES (29, '0000-00-00 00:00:00', 'NO_ZERO_DATE');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '0000-00-00 00:00:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (30, '2025-00-30 00:00:00', 'NO_ZERO_DATE');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-00-30 00:00:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (31, '2025-02-30 00:00:00', 'NO_ZERO_DATE');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-02-30 00:00:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (32, '2025-03-30 01:59:59', 'NO_ZERO_DATE');
|
|
INSERT INTO t VALUES (33, '2025-03-30 02:00:00', 'NO_ZERO_DATE');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-03-30 02:00:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (34, '2025-03-30 02:30:00', 'NO_ZERO_DATE');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-03-30 02:30:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (35, '2025-03-30 03:00:00', 'NO_ZERO_DATE');
|
|
SET SQL_MODE = 'NO_ZERO_DATE,ALLOW_INVALID_DATES';
|
|
INSERT INTO t VALUES (36, '0000-00-00 00:00:00', 'NO_ZERO_DATE,ALLOW_INVALID_DATES');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '0000-00-00 00:00:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (37, '2025-00-30 00:00:00', 'NO_ZERO_DATE,ALLOW_INVALID_DATES');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-00-30 00:00:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (38, '2025-02-30 00:00:00', 'NO_ZERO_DATE,ALLOW_INVALID_DATES');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-02-30 00:00:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (39, '2025-03-30 01:59:59', 'NO_ZERO_DATE,ALLOW_INVALID_DATES');
|
|
INSERT INTO t VALUES (40, '2025-03-30 02:00:00', 'NO_ZERO_DATE,ALLOW_INVALID_DATES');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-03-30 02:00:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (41, '2025-03-30 02:30:00', 'NO_ZERO_DATE,ALLOW_INVALID_DATES');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-03-30 02:30:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (42, '2025-03-30 03:00:00', 'NO_ZERO_DATE,ALLOW_INVALID_DATES');
|
|
SET SQL_MODE = 'NO_ZERO_DATE,NO_ZERO_IN_DATE';
|
|
INSERT INTO t VALUES (43, '0000-00-00 00:00:00', 'NO_ZERO_DATE,NO_ZERO_IN_DATE');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '0000-00-00 00:00:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (44, '2025-00-30 00:00:00', 'NO_ZERO_DATE,NO_ZERO_IN_DATE');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-00-30 00:00:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (45, '2025-02-30 00:00:00', 'NO_ZERO_DATE,NO_ZERO_IN_DATE');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-02-30 00:00:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (46, '2025-03-30 01:59:59', 'NO_ZERO_DATE,NO_ZERO_IN_DATE');
|
|
INSERT INTO t VALUES (47, '2025-03-30 02:00:00', 'NO_ZERO_DATE,NO_ZERO_IN_DATE');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-03-30 02:00:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (48, '2025-03-30 02:30:00', 'NO_ZERO_DATE,NO_ZERO_IN_DATE');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-03-30 02:30:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (49, '2025-03-30 03:00:00', 'NO_ZERO_DATE,NO_ZERO_IN_DATE');
|
|
SET SQL_MODE = 'NO_ZERO_DATE,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES';
|
|
INSERT INTO t VALUES (50, '0000-00-00 00:00:00', 'NO_ZERO_DATE,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '0000-00-00 00:00:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (51, '2025-00-30 00:00:00', 'NO_ZERO_DATE,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-00-30 00:00:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (52, '2025-02-30 00:00:00', 'NO_ZERO_DATE,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-02-30 00:00:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (53, '2025-03-30 01:59:59', 'NO_ZERO_DATE,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES');
|
|
INSERT INTO t VALUES (54, '2025-03-30 02:00:00', 'NO_ZERO_DATE,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-03-30 02:00:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (55, '2025-03-30 02:30:00', 'NO_ZERO_DATE,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-03-30 02:30:00' for column 'ts' at row 1
|
|
INSERT INTO t VALUES (56, '2025-03-30 03:00:00', 'NO_ZERO_DATE,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES');
|
|
SELECT * FROM t ORDER BY id;
|
|
id ts mode
|
|
1 0000-00-00 00:00:00
|
|
2 0000-00-00 00:00:00
|
|
3 0000-00-00 00:00:00
|
|
4 2025-03-30 01:59:59
|
|
5 2025-03-30 03:00:00
|
|
6 2025-03-30 03:00:00
|
|
7 2025-03-30 03:00:00
|
|
8 0000-00-00 00:00:00 ALLOW_INVALID_DATES
|
|
9 0000-00-00 00:00:00 ALLOW_INVALID_DATES
|
|
10 0000-00-00 00:00:00 ALLOW_INVALID_DATES
|
|
11 2025-03-30 01:59:59 ALLOW_INVALID_DATES
|
|
12 2025-03-30 03:00:00 ALLOW_INVALID_DATES
|
|
13 2025-03-30 03:00:00 ALLOW_INVALID_DATES
|
|
14 2025-03-30 03:00:00 ALLOW_INVALID_DATES
|
|
15 0000-00-00 00:00:00 NO_ZERO_IN_DATE
|
|
16 0000-00-00 00:00:00 NO_ZERO_IN_DATE
|
|
17 0000-00-00 00:00:00 NO_ZERO_IN_DATE
|
|
18 2025-03-30 01:59:59 NO_ZERO_IN_DATE
|
|
19 2025-03-30 03:00:00 NO_ZERO_IN_DATE
|
|
20 2025-03-30 03:00:00 NO_ZERO_IN_DATE
|
|
21 2025-03-30 03:00:00 NO_ZERO_IN_DATE
|
|
22 0000-00-00 00:00:00 NO_ZERO_IN_DATE,ALLOW_INVALID_DATES
|
|
23 0000-00-00 00:00:00 NO_ZERO_IN_DATE,ALLOW_INVALID_DATES
|
|
24 0000-00-00 00:00:00 NO_ZERO_IN_DATE,ALLOW_INVALID_DATES
|
|
25 2025-03-30 01:59:59 NO_ZERO_IN_DATE,ALLOW_INVALID_DATES
|
|
26 2025-03-30 03:00:00 NO_ZERO_IN_DATE,ALLOW_INVALID_DATES
|
|
27 2025-03-30 03:00:00 NO_ZERO_IN_DATE,ALLOW_INVALID_DATES
|
|
28 2025-03-30 03:00:00 NO_ZERO_IN_DATE,ALLOW_INVALID_DATES
|
|
29 0000-00-00 00:00:00 NO_ZERO_DATE
|
|
30 0000-00-00 00:00:00 NO_ZERO_DATE
|
|
31 0000-00-00 00:00:00 NO_ZERO_DATE
|
|
32 2025-03-30 01:59:59 NO_ZERO_DATE
|
|
33 2025-03-30 03:00:00 NO_ZERO_DATE
|
|
34 2025-03-30 03:00:00 NO_ZERO_DATE
|
|
35 2025-03-30 03:00:00 NO_ZERO_DATE
|
|
36 0000-00-00 00:00:00 NO_ZERO_DATE,ALLOW_INVALID_DATES
|
|
37 0000-00-00 00:00:00 NO_ZERO_DATE,ALLOW_INVALID_DATES
|
|
38 0000-00-00 00:00:00 NO_ZERO_DATE,ALLOW_INVALID_DATES
|
|
39 2025-03-30 01:59:59 NO_ZERO_DATE,ALLOW_INVALID_DATES
|
|
40 2025-03-30 03:00:00 NO_ZERO_DATE,ALLOW_INVALID_DATES
|
|
41 2025-03-30 03:00:00 NO_ZERO_DATE,ALLOW_INVALID_DATES
|
|
42 2025-03-30 03:00:00 NO_ZERO_DATE,ALLOW_INVALID_DATES
|
|
43 0000-00-00 00:00:00 NO_ZERO_DATE,NO_ZERO_IN_DATE
|
|
44 0000-00-00 00:00:00 NO_ZERO_DATE,NO_ZERO_IN_DATE
|
|
45 0000-00-00 00:00:00 NO_ZERO_DATE,NO_ZERO_IN_DATE
|
|
46 2025-03-30 01:59:59 NO_ZERO_DATE,NO_ZERO_IN_DATE
|
|
47 2025-03-30 03:00:00 NO_ZERO_DATE,NO_ZERO_IN_DATE
|
|
48 2025-03-30 03:00:00 NO_ZERO_DATE,NO_ZERO_IN_DATE
|
|
49 2025-03-30 03:00:00 NO_ZERO_DATE,NO_ZERO_IN_DATE
|
|
50 0000-00-00 00:00:00 NO_ZERO_DATE,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES
|
|
51 0000-00-00 00:00:00 NO_ZERO_DATE,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES
|
|
52 0000-00-00 00:00:00 NO_ZERO_DATE,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES
|
|
53 2025-03-30 01:59:59 NO_ZERO_DATE,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES
|
|
54 2025-03-30 03:00:00 NO_ZERO_DATE,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES
|
|
55 2025-03-30 03:00:00 NO_ZERO_DATE,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES
|
|
56 2025-03-30 03:00:00 NO_ZERO_DATE,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES
|
|
DROP TABLE t;
|
|
SET @@time_zone = @old_time_zone;
|
|
SET @@sql_mode = @old_sql_mode;
|
|
SET @old_time_zone = @@time_zone;
|
|
SET @@time_zone = 'Europe/Amsterdam';
|
|
SET @old_sql_mode = @@sql_mode;
|
|
create table t (ts timestamp);
|
|
set time_zone = 'Europe/Amsterdam';
|
|
set sql_mode = 'strict_trans_tables,no_zero_date,no_zero_in_date,error_for_division_by_zero';
|
|
insert into t values ('2025-03-30 02:00:00');
|
|
Error 1292 (22007): Incorrect timestamp value: '2025-03-30 02:00:00' for column 'ts' at row 1
|
|
select * from t;
|
|
ts
|
|
insert ignore into t values ('2025-03-30 02:00:00');
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2025-03-30 02:00:00' for column 'ts' at row 1
|
|
select * from t;
|
|
ts
|
|
2025-03-30 03:00:00
|
|
DROP TABLE t;
|
|
SET @@time_zone = @old_time_zone;
|
|
SET @@sql_mode = @old_sql_mode;
|