565 lines
12 KiB
Plaintext
565 lines
12 KiB
Plaintext
set @@session.tidb_executor_concurrency = 4;
|
|
set @@session.tidb_hash_join_concurrency = 5;
|
|
set @@session.tidb_distsql_scan_concurrency = 15;
|
|
set @@tidb_partition_prune_mode = dynamic;
|
|
drop table if exists t;
|
|
create table t (a int primary key, b int, index idx_b (b)) ;
|
|
analyze table t;
|
|
insert t value (2, 3), (4, 8), (6, 8);
|
|
begin;
|
|
select * from t order by a;
|
|
a b
|
|
2 3
|
|
4 8
|
|
6 8
|
|
insert t values (1, 5), (3, 4), (7, 6);
|
|
select * from information_schema.columns;
|
|
select * from t order by a;
|
|
a b
|
|
1 5
|
|
2 3
|
|
3 4
|
|
4 8
|
|
6 8
|
|
7 6
|
|
select * from t where a = 1;
|
|
a b
|
|
1 5
|
|
select * from t order by a desc;
|
|
a b
|
|
7 6
|
|
6 8
|
|
4 8
|
|
3 4
|
|
2 3
|
|
1 5
|
|
select * from t order by b, a;
|
|
a b
|
|
2 3
|
|
3 4
|
|
1 5
|
|
7 6
|
|
4 8
|
|
6 8
|
|
select * from t order by b desc, a desc;
|
|
a b
|
|
6 8
|
|
4 8
|
|
7 6
|
|
1 5
|
|
3 4
|
|
2 3
|
|
select b from t where b = 8 order by b desc;
|
|
b
|
|
8
|
|
8
|
|
delete from t where a = 2 or a = 3;
|
|
select * from t order by a;
|
|
a b
|
|
1 5
|
|
4 8
|
|
6 8
|
|
7 6
|
|
select * from t order by a desc;
|
|
a b
|
|
7 6
|
|
6 8
|
|
4 8
|
|
1 5
|
|
select * from t order by b, a;
|
|
a b
|
|
1 5
|
|
7 6
|
|
4 8
|
|
6 8
|
|
select * from t order by b desc, a desc;
|
|
a b
|
|
6 8
|
|
4 8
|
|
7 6
|
|
1 5
|
|
insert t values (2, 3), (3, 4);
|
|
select * from t order by a;
|
|
a b
|
|
1 5
|
|
2 3
|
|
3 4
|
|
4 8
|
|
6 8
|
|
7 6
|
|
select * from t order by a desc;
|
|
a b
|
|
7 6
|
|
6 8
|
|
4 8
|
|
3 4
|
|
2 3
|
|
1 5
|
|
select * from t order by b, a;
|
|
a b
|
|
2 3
|
|
3 4
|
|
1 5
|
|
7 6
|
|
4 8
|
|
6 8
|
|
select * from t order by b desc, a desc;
|
|
a b
|
|
6 8
|
|
4 8
|
|
7 6
|
|
1 5
|
|
3 4
|
|
2 3
|
|
truncate table t;
|
|
select * from t;
|
|
a b
|
|
insert t values (1, 2);
|
|
select * from t;
|
|
a b
|
|
1 2
|
|
truncate table t;
|
|
insert t values (3, 4);
|
|
select * from t;
|
|
a b
|
|
3 4
|
|
commit;
|
|
drop table if exists t;
|
|
create table t (a int primary key, b int, index idx_b (b)) PARTITION BY HASH(a) partitions 4;
|
|
analyze table t;
|
|
insert t value (2, 3), (4, 8), (6, 8);
|
|
begin;
|
|
select * from t order by a;
|
|
a b
|
|
2 3
|
|
4 8
|
|
6 8
|
|
insert t values (1, 5), (3, 4), (7, 6);
|
|
select * from information_schema.columns;
|
|
select * from t order by a;
|
|
a b
|
|
1 5
|
|
2 3
|
|
3 4
|
|
4 8
|
|
6 8
|
|
7 6
|
|
select * from t where a = 1;
|
|
a b
|
|
1 5
|
|
select * from t order by a desc;
|
|
a b
|
|
7 6
|
|
6 8
|
|
4 8
|
|
3 4
|
|
2 3
|
|
1 5
|
|
select * from t order by b, a;
|
|
a b
|
|
2 3
|
|
3 4
|
|
1 5
|
|
7 6
|
|
4 8
|
|
6 8
|
|
select * from t order by b desc, a desc;
|
|
a b
|
|
6 8
|
|
4 8
|
|
7 6
|
|
1 5
|
|
3 4
|
|
2 3
|
|
select b from t where b = 8 order by b desc;
|
|
b
|
|
8
|
|
8
|
|
delete from t where a = 2 or a = 3;
|
|
select * from t order by a;
|
|
a b
|
|
1 5
|
|
4 8
|
|
6 8
|
|
7 6
|
|
select * from t order by a desc;
|
|
a b
|
|
7 6
|
|
6 8
|
|
4 8
|
|
1 5
|
|
select * from t order by b, a;
|
|
a b
|
|
1 5
|
|
7 6
|
|
4 8
|
|
6 8
|
|
select * from t order by b desc, a desc;
|
|
a b
|
|
6 8
|
|
4 8
|
|
7 6
|
|
1 5
|
|
insert t values (2, 3), (3, 4);
|
|
select * from t order by a;
|
|
a b
|
|
1 5
|
|
2 3
|
|
3 4
|
|
4 8
|
|
6 8
|
|
7 6
|
|
select * from t order by a desc;
|
|
a b
|
|
7 6
|
|
6 8
|
|
4 8
|
|
3 4
|
|
2 3
|
|
1 5
|
|
select * from t order by b, a;
|
|
a b
|
|
2 3
|
|
3 4
|
|
1 5
|
|
7 6
|
|
4 8
|
|
6 8
|
|
select * from t order by b desc, a desc;
|
|
a b
|
|
6 8
|
|
4 8
|
|
7 6
|
|
1 5
|
|
3 4
|
|
2 3
|
|
truncate table t;
|
|
select * from t;
|
|
a b
|
|
insert t values (1, 2);
|
|
select * from t;
|
|
a b
|
|
1 2
|
|
truncate table t;
|
|
insert t values (3, 4);
|
|
select * from t;
|
|
a b
|
|
3 4
|
|
commit;
|
|
drop table if exists t;
|
|
create table t (a int, b int);
|
|
insert t values (2, 3), (4, 5), (6, 7);
|
|
begin;
|
|
insert t values (0, 1);
|
|
select * from t where b = 3;
|
|
a b
|
|
2 3
|
|
commit;
|
|
drop table if exists t;
|
|
create table t(a json, b bigint);
|
|
begin;
|
|
insert into t values("\"1\"", 1);
|
|
select * from t;
|
|
a b
|
|
"1" 1
|
|
commit;
|
|
drop table if exists t;
|
|
create table t(a int, b int, c int, d int, index idx(c, d));
|
|
begin;
|
|
insert into t values(1, 2, 3, 4);
|
|
select * from t use index(idx) where c > 1 and d = 4;
|
|
a b c d
|
|
1 2 3 4
|
|
commit;
|
|
drop table if exists t;
|
|
CREATE TABLE t (c1 smallint(6) NOT NULL, c2 char(5) DEFAULT NULL) PARTITION BY RANGE ( c1 ) (
|
|
PARTITION p0 VALUES LESS THAN (10),
|
|
PARTITION p1 VALUES LESS THAN (20),
|
|
PARTITION p2 VALUES LESS THAN (30),
|
|
PARTITION p3 VALUES LESS THAN (MAXVALUE)
|
|
);
|
|
begin;
|
|
insert into t values (1, 1);
|
|
select * from t;
|
|
c1 c2
|
|
1 1
|
|
select * from t where c1 < 5;
|
|
c1 c2
|
|
1 1
|
|
select c2 from t;
|
|
c2
|
|
1
|
|
commit;
|
|
drop table if exists t;
|
|
create table t (a int, b int as (a+1), c int as (b+1), index(c));
|
|
begin;
|
|
insert into t values (1, default, default), (2, default, default), (3, default, default);
|
|
select * from t;
|
|
a b c
|
|
1 2 3
|
|
2 3 4
|
|
3 4 5
|
|
select b from t;
|
|
b
|
|
2
|
|
3
|
|
4
|
|
select c from t;
|
|
c
|
|
3
|
|
4
|
|
5
|
|
select a from t;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
select c from t where c > 3;
|
|
c
|
|
4
|
|
5
|
|
select c from t order by c;
|
|
c
|
|
3
|
|
4
|
|
5
|
|
select * from t where c > 3;
|
|
a b c
|
|
2 3 4
|
|
3 4 5
|
|
select a, b from t use index(c) where c > 3;
|
|
a b
|
|
2 3
|
|
3 4
|
|
select a, c from t use index(c) where c > 3;
|
|
a c
|
|
2 4
|
|
3 5
|
|
select b, c from t use index(c) where c > 3;
|
|
b c
|
|
3 4
|
|
4 5
|
|
delete from t where c > 4;
|
|
select * from t;
|
|
a b c
|
|
1 2 3
|
|
2 3 4
|
|
update t set a = 3 where b > 1;
|
|
select * from t;
|
|
a b c
|
|
3 4 5
|
|
3 4 5
|
|
commit;
|
|
select * from t;
|
|
a b c
|
|
3 4 5
|
|
3 4 5
|
|
begin;
|
|
insert into t values (1, default, default), (2, default, default), (3, default, default);
|
|
select * from t;
|
|
a b c
|
|
3 4 5
|
|
3 4 5
|
|
1 2 3
|
|
2 3 4
|
|
3 4 5
|
|
select b from t;
|
|
b
|
|
4
|
|
4
|
|
2
|
|
3
|
|
4
|
|
select c from t;
|
|
c
|
|
3
|
|
4
|
|
5
|
|
5
|
|
5
|
|
select a from t;
|
|
a
|
|
3
|
|
3
|
|
1
|
|
2
|
|
3
|
|
select c from t where c > 3;
|
|
c
|
|
4
|
|
5
|
|
5
|
|
5
|
|
select c from t order by c;
|
|
c
|
|
3
|
|
4
|
|
5
|
|
5
|
|
5
|
|
select * from t where c > 3;
|
|
a b c
|
|
3 4 5
|
|
3 4 5
|
|
2 3 4
|
|
3 4 5
|
|
select a, b from t use index(c) where c > 3;
|
|
a b
|
|
2 3
|
|
3 4
|
|
3 4
|
|
3 4
|
|
select a, c from t use index(c) where c > 3;
|
|
a c
|
|
2 4
|
|
3 5
|
|
3 5
|
|
3 5
|
|
select b, c from t use index(c) where c > 3;
|
|
b c
|
|
3 4
|
|
4 5
|
|
4 5
|
|
4 5
|
|
delete from t where c > 4;
|
|
select * from t;
|
|
a b c
|
|
1 2 3
|
|
2 3 4
|
|
update t set a = 3 where b > 2;
|
|
select * from t;
|
|
a b c
|
|
1 2 3
|
|
3 4 5
|
|
commit;
|
|
set @@session.tidb_executor_concurrency = default;
|
|
set @@session.tidb_hash_join_concurrency = default;
|
|
set @@session.tidb_distsql_scan_concurrency = default;
|
|
set @@tidb_partition_prune_mode = default;
|
|
drop table if exists ta, tb;
|
|
create table ta (a varchar(20));
|
|
insert ta values ('1'), ('2');
|
|
create table tb (a varchar(20));
|
|
begin;
|
|
select * from ta where a = 1;
|
|
a
|
|
1
|
|
insert tb values ('0');
|
|
select * from ta where a = 1;
|
|
a
|
|
1
|
|
rollback;
|
|
drop table if exists t;
|
|
create table t(a year(4), b enum('2','k','4','nsy','wmlgy','alkr7'), primary key (a), key idx(b));
|
|
insert into t values (2033, 'alkr7');
|
|
begin;
|
|
insert into t set a = '2011', b = '4';
|
|
select /*+ USE_INDEX_MERGE(t, primary, idx) */b from t where not( b in ( 'alkr7' ) ) or not( a in ( '1989' ,'1970' ) );
|
|
b
|
|
4
|
|
alkr7
|
|
rollback;
|
|
drop table if exists t_vwvgdc;
|
|
CREATE TABLE t_vwvgdc (wkey int, pkey int NOT NULL, c_rdsfbc double DEFAULT NULL, PRIMARY KEY (`pkey`));
|
|
insert into t_vwvgdc values (2, 15000, 61.75);
|
|
BEGIN OPTIMISTIC;
|
|
insert into t_vwvgdc (wkey, pkey, c_rdsfbc) values (155, 228000, 99.50);
|
|
select pkey from t_vwvgdc where 0 <> 0 union select pkey from t_vwvgdc;
|
|
pkey
|
|
15000
|
|
228000
|
|
rollback;
|
|
drop table if exists t;
|
|
create table t ( c_int int, c_str varchar(40), primary key(c_int, c_str) );
|
|
begin;
|
|
insert into t values (1, 'amazing almeida'), (2, 'boring bardeen'), (3, 'busy wescoff');
|
|
select c_int, (select t1.c_int from t t1 where t1.c_int = 3 and t1.c_int > t.c_int order by t1.c_int limit 1) x from t;
|
|
c_int x
|
|
1 3
|
|
2 3
|
|
3 NULL
|
|
commit;
|
|
select c_int, (select t1.c_int from t t1 where t1.c_int = 3 and t1.c_int > t.c_int order by t1.c_int limit 1) x from t;
|
|
c_int x
|
|
1 3
|
|
2 3
|
|
3 NULL
|
|
drop table if exists t, t1;
|
|
create table t1(c_int int);
|
|
create table t(c_int int);
|
|
insert into t values(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
begin;
|
|
insert into t1 values(18);
|
|
select (select min(t1.c_int) from t1 where t1.c_int > t.c_int), (select max(t1.c_int) from t1 where t1.c_int> t.c_int), (select sum(t1.c_int) from t1 where t1.c_int> t.c_int) from t;
|
|
(select min(t1.c_int) from t1 where t1.c_int > t.c_int) (select max(t1.c_int) from t1 where t1.c_int> t.c_int) (select sum(t1.c_int) from t1 where t1.c_int> t.c_int)
|
|
18 18 18
|
|
18 18 18
|
|
18 18 18
|
|
18 18 18
|
|
18 18 18
|
|
18 18 18
|
|
18 18 18
|
|
18 18 18
|
|
18 18 18
|
|
rollback;
|
|
DROP TABLE IF EXISTS `t`;
|
|
CREATE TABLE `t` ( `c_int` int(11) NOT NULL, `c_str` varchar(40) NOT NULL, `c_datetime` datetime NOT NULL, PRIMARY KEY (`c_int`,`c_str`,`c_datetime`), KEY `c_str` (`c_str`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
|
|
INSERT INTO `t` VALUES (1,'cool pasteur','2020-04-21 19:01:04'),(3,'friendly stonebraker','2020-06-09 18:58:00'),(5,'happy shannon','2020-02-29 21:39:08'),(6,'competent torvalds','2020-05-24 04:18:45'),(7,'fervent kapitsa','2020-05-21 16:58:12'),(8,'quirky jennings','2020-03-12 12:52:58'),(9,'adoring swartz','2020-04-19 02:20:32'),(14,'intelligent keller','2020-01-08 09:47:42'),(15,'vibrant zhukovsky','2020-04-15 15:15:55'),(18,'keen chatterjee','2020-02-09 06:39:31'),(20,'elastic gauss','2020-03-01 13:34:06'),(21,'affectionate margulis','2020-06-20 10:20:29'),(27,'busy keldysh','2020-05-21 09:10:45'),(31,'flamboyant banach','2020-03-04 21:28:44'),(39,'keen banach','2020-06-09 03:07:57'),(41,'nervous gagarin','2020-06-12 23:43:04'),(47,'wonderful chebyshev','2020-04-15 14:51:17'),(50,'reverent brahmagupta','2020-06-25 21:50:52'),(52,'suspicious elbakyan','2020-05-28 04:55:34'),(55,'epic lichterman','2020-05-16 19:24:09'),(57,'determined taussig','2020-06-18 22:51:37');
|
|
DROP TABLE IF EXISTS `t1`;
|
|
CREATE TABLE `t1` ( `c_int` int(11) DEFAULT NULL, `c_str` varchar(40) NOT NULL, `c_datetime` datetime DEFAULT NULL, PRIMARY KEY (`c_str`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
|
|
INSERT INTO `t1` VALUES (19,'nervous johnson','2020-05-04 13:15:19'),(22,'pedantic tu','2020-02-19 09:32:44'),(24,'wizardly robinson','2020-02-03 18:39:36'),(33,'eager stonebraker','2020-05-03 08:20:54'),(34,'zen taussig','2020-06-29 01:18:48'),(36,'epic ganguly','2020-04-23 17:25:13'),(38,'objective euclid','2020-05-21 01:04:27'),(40,'infallible hodgkin','2020-05-07 03:52:52'),(43,'wizardly hellman','2020-04-11 20:20:05'),(46,'inspiring hoover','2020-06-28 14:47:34'),(48,'amazing cerf','2020-05-15 08:04:32'),(49,'objective hermann','2020-04-25 18:01:06'),(51,'upbeat spence','2020-01-27 21:59:54'),(53,'hardcore nightingale','2020-01-20 18:57:37'),(54,'silly hellman','2020-06-24 00:22:47'),(56,'elastic driscoll','2020-02-27 22:46:57'),(58,'nifty buck','2020-03-12 03:56:16');
|
|
begin;
|
|
insert into t values (59, 'suspicious feistel', '2020-01-29 19:52:14');
|
|
insert into t1 values (60, 'practical thompson', '2020-03-25 04:33:10');
|
|
select c_int, c_str from t where (select count(*) from t1 where t1.c_int in (t.c_int, t.c_int + 2, t.c_int + 10)) > 2;
|
|
c_int c_str
|
|
rollback;
|
|
drop table if exists t;
|
|
create table t(a int primary key, b int);
|
|
begin;
|
|
insert into t values (2, 2),(3, 3),(4, 4),(5, 5),(6, 6),(7, 7),(8, 8),(9, 9),(10, 10),(11, 11),(12, 12),(13, 13),(14, 14),(15, 15),(16, 16),(17, 17),(18, 18),(19, 19),(20, 20),(21, 21),(22, 22),(23, 23),(24, 24),(25, 25),(26, 26),(27, 27),(28, 28),(29, 29),(30, 30),(31, 31),(32, 32),(33, 33),(34, 34),(35, 35),(36, 36),(37, 37),(38, 38),(39, 39),(40, 40),(41, 41),(42, 42),(43, 43),(44, 44),(45, 45),(46, 46),(47, 47),(48, 48),(49, 49),(50, 50),(51, 51),(52, 52),(53, 53),(54, 54),(55, 55),(56, 56),(57, 57),(58, 58),(59, 59),(60, 60),(61, 61),(62, 62),(63, 63),(64, 64),(65, 65),(66, 66),(67, 67),(68, 68),(69, 69),(70, 70),(71, 71),(72, 72),(73, 73),(74, 74),(75, 75),(76, 76),(77, 77),(78, 78),(79, 79),(80, 80),(81, 81),(82, 82),(83, 83),(84, 84),(85, 85),(86, 86),(87, 87),(88, 88),(89, 89),(90, 90),(91, 91),(92, 92),(93, 93),(94, 94),(95, 95),(96, 96),(97, 97),(98, 98),(99, 99);
|
|
commit;
|
|
set tidb_distsql_scan_concurrency = 1;
|
|
set tidb_index_lookup_join_concurrency = 1;
|
|
set tidb_projection_concurrency=1;
|
|
set tidb_init_chunk_size=1;
|
|
set tidb_max_chunk_size=32;
|
|
begin;
|
|
insert into t values (1, 1);
|
|
update /*+ INL_JOIN(t1) */ t t1, (select a, b from t) t2 set t1.b = t2.b where t1.a = t2.a + 10;
|
|
select a, a-b from t where a > 10 and a - b != 10;
|
|
a a-b
|
|
rollback;
|
|
set tidb_distsql_scan_concurrency = default;
|
|
set tidb_index_lookup_join_concurrency = default;
|
|
set tidb_projection_concurrency=default;
|
|
set tidb_init_chunk_size=default;
|
|
set tidb_max_chunk_size=default;
|
|
drop table if exists t;
|
|
create table t (a varchar(10) primary key,b int);
|
|
begin;
|
|
insert into t values ('a', 1), ('b', 3), ('a', 2) on duplicate key update b = b + 1;
|
|
commit;
|
|
admin check table t;
|
|
set autocommit=0;
|
|
insert into t values ('a', 1), ('b', 3), ('a', 2) on duplicate key update b = b + 1;
|
|
set autocommit=1;
|
|
admin check table t;
|
|
drop table if exists t;
|
|
create table t (a varchar(10),b int, unique index(a));
|
|
begin;
|
|
insert into t values ('a', 1), ('b', 3), ('a', 2) on duplicate key update b = b + 1;
|
|
commit;
|
|
admin check table t;
|
|
set autocommit=0;
|
|
insert into t values ('a', 1), ('b', 3), ('a', 2) on duplicate key update b = b + 1;
|
|
set autocommit=1;
|
|
admin check table t;
|
|
drop table if exists t;
|
|
create table t (a int,b int, unique index(a));
|
|
begin;
|
|
insert into t values (1, 1), (2, 2), (1, 3) on duplicate key update a = a + 1;
|
|
Error 1062 (23000): Duplicate entry '2' for key 't.a'
|
|
commit;
|
|
admin check table t;
|