mirror of
https://github.com/MariaDB/server.git
synced 2025-10-28 17:36:15 +01:00
68c6489bc7
Part of MDEV-36274 Implement IDENTITY column AUTO_INCREMENT column has to a column be a part of key in order to work. Moreover, if it's not the first key_part, its behavior is very specific, i.e. it can generate repeated values. IDENTITY should work without the key, and moreover should generate new values without repetition. On the other hand, having at least any key is currently an inevitable requirement. Instead of failing, let's implicitly generate a key if needed.
314 lines
10 KiB
Text
314 lines
10 KiB
Text
create table t(x int generated by default on null as identity, key(x));
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`x` int(11) GENERATED BY DEFAULT ON NULL AS IDENTITY START WITH 1 INCREMENT BY 1 NOMINVALUE NOMAXVALUE NOCACHE NOCYCLE NOORDER,
|
|
KEY `x` (`x`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
insert t(x) values(null);
|
|
drop table t;
|
|
create table t(x int generated by default as identity, key(x));
|
|
insert t() values();
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`x` int(11) GENERATED BY DEFAULT AS IDENTITY START WITH 2 INCREMENT BY 1 NOMINVALUE NOMAXVALUE NOCACHE NOCYCLE NOORDER,
|
|
KEY `x` (`x`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
insert t(x) values(default),(default),(default);
|
|
insert t(x) values(-1), (-33);
|
|
insert t() values();
|
|
select * from t;
|
|
x
|
|
-33
|
|
-1
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
flush table t;
|
|
insert t() values(42);
|
|
insert t() values();
|
|
select * from t;
|
|
x
|
|
-33
|
|
-1
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
42
|
|
43
|
|
alter table t force;
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`x` int(11) GENERATED BY DEFAULT AS IDENTITY START WITH 44 INCREMENT BY 1 NOMINVALUE NOMAXVALUE NOCACHE NOCYCLE NOORDER,
|
|
KEY `x` (`x`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
insert t() values();
|
|
select * from t;
|
|
x
|
|
-33
|
|
-1
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
42
|
|
43
|
|
44
|
|
# Begin testing mysqldump output + restore
|
|
# Create 'original table name - <table>_orig
|
|
SET @orig_table_name = CONCAT('test.t', '_orig');
|
|
# Rename original table
|
|
ALTER TABLE test.t RENAME to test.t_orig;
|
|
# Recreate table from mysqldump output
|
|
# Compare original and recreated tables
|
|
# Recreated table: test.t
|
|
# Original table: test.t_orig
|
|
include/diff_tables.inc [test.t, test.t_orig]
|
|
# Cleanup
|
|
DROP TABLE test.t, test.t_orig;
|
|
create table t(x int generated by default as identity start with 10 increment by 101, key(x));
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`x` int(11) GENERATED BY DEFAULT AS IDENTITY START WITH 110 INCREMENT BY 101 NOMINVALUE NOMAXVALUE NOCACHE NOCYCLE NOORDER,
|
|
KEY `x` (`x`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
insert t() values(),(),();
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`x` int(11) GENERATED BY DEFAULT AS IDENTITY START WITH 313 INCREMENT BY 101 NOMINVALUE NOMAXVALUE NOCACHE NOCYCLE NOORDER,
|
|
KEY `x` (`x`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
select * from t;
|
|
x
|
|
10
|
|
111
|
|
212
|
|
insert t(x) values(-1), (-33);
|
|
insert t() values();
|
|
select * from t;
|
|
x
|
|
-33
|
|
-1
|
|
10
|
|
111
|
|
212
|
|
313
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`x` int(11) GENERATED BY DEFAULT AS IDENTITY START WITH 414 INCREMENT BY 101 NOMINVALUE NOMAXVALUE NOCACHE NOCYCLE NOORDER,
|
|
KEY `x` (`x`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
alter table t add y int;
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`x` int(11) GENERATED BY DEFAULT AS IDENTITY START WITH 414 INCREMENT BY 101 NOMINVALUE NOMAXVALUE NOCACHE NOCYCLE NOORDER,
|
|
`y` int(11) DEFAULT NULL,
|
|
KEY `x` (`x`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
insert t() values();
|
|
select * from t;
|
|
x y
|
|
10 NULL
|
|
111 NULL
|
|
212 NULL
|
|
-1 NULL
|
|
-33 NULL
|
|
313 NULL
|
|
414 NULL
|
|
# Begin testing mysqldump output + restore
|
|
# Create 'original table name - <table>_orig
|
|
SET @orig_table_name = CONCAT('test.t', '_orig');
|
|
# Rename original table
|
|
ALTER TABLE test.t RENAME to test.t_orig;
|
|
# Recreate table from mysqldump output
|
|
# Compare original and recreated tables
|
|
# Recreated table: test.t
|
|
# Original table: test.t_orig
|
|
include/diff_tables.inc [test.t, test.t_orig]
|
|
# Cleanup
|
|
DROP TABLE test.t, test.t_orig;
|
|
# Next insert id is adjusted to the explicit value
|
|
create table t(x int generated by default as identity start with 10, key(x));
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`x` int(11) GENERATED BY DEFAULT AS IDENTITY START WITH 10 INCREMENT BY 1 NOMINVALUE NOMAXVALUE NOCACHE NOCYCLE NOORDER,
|
|
KEY `x` (`x`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
insert t(x) values(default),(20),(default);
|
|
insert t(x) values(default),(21),(default);
|
|
select * from t;
|
|
x
|
|
10
|
|
20
|
|
21
|
|
21
|
|
22
|
|
23
|
|
insert t(x) values(default),(25);
|
|
# We should see STARTS WITH 26
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`x` int(11) GENERATED BY DEFAULT AS IDENTITY START WITH 26 INCREMENT BY 1 NOMINVALUE NOMAXVALUE NOCACHE NOCYCLE NOORDER,
|
|
KEY `x` (`x`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
# Begin testing mysqldump output + restore
|
|
# Create 'original table name - <table>_orig
|
|
SET @orig_table_name = CONCAT('test.t', '_orig');
|
|
# Rename original table
|
|
ALTER TABLE test.t RENAME to test.t_orig;
|
|
# Recreate table from mysqldump output
|
|
# Compare original and recreated tables
|
|
# Recreated table: test.t
|
|
# Original table: test.t_orig
|
|
include/diff_tables.inc [test.t, test.t_orig]
|
|
# Cleanup
|
|
DROP TABLE test.t, test.t_orig;
|
|
# GENERATED ALWAYS
|
|
create table t(x int generated always as identity start with 100 increment by 200, key(x));
|
|
insert into t() values();
|
|
insert into t(x) values(2);
|
|
ERROR HY000: The value specified for generated column 'x' in table 't' has been ignored
|
|
select * from t;
|
|
x
|
|
100
|
|
drop table t;
|
|
insert into t() values();
|
|
select * from t;
|
|
x
|
|
100
|
|
300
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`x` int(11) NOT NULL GENERATED ALWAYS AS IDENTITY START WITH 500 INCREMENT BY 200 NOMINVALUE NOMAXVALUE NOCACHE NOCYCLE NOORDER,
|
|
KEY `x` (`x`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
drop table t;
|
|
# Expression with IDENTITY
|
|
create table t(x int generated by default as identity increment by 2, key(x),
|
|
check (x % 2 != 0), y int default(x * 2), z int as (x-1));
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`x` int(11) GENERATED BY DEFAULT AS IDENTITY START WITH 2 INCREMENT BY 2 NOMINVALUE NOMAXVALUE NOCACHE NOCYCLE NOORDER,
|
|
`y` int(11) DEFAULT (`x` * 2),
|
|
`z` int(11) GENERATED ALWAYS AS (`x` - 1) VIRTUAL,
|
|
KEY `x` (`x`),
|
|
CONSTRAINT `CONSTRAINT_1` CHECK (`x` MOD 2 <> 0)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
insert t() values(),(),();
|
|
select * from t;
|
|
x y z
|
|
1 2 0
|
|
3 6 2
|
|
5 10 4
|
|
alter table t add check(x < 5);
|
|
ERROR 23000: CONSTRAINT `CONSTRAINT_2` failed for `test`.`t`
|
|
alter table t add check(x < 6);
|
|
drop table t;
|
|
# Limitations and syntax
|
|
create table t(x int generated by default as identity increment by -1 minvalue 9, key(x));
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`x` int(11) GENERATED BY DEFAULT AS IDENTITY START WITH 1 INCREMENT BY 1 MINVALUE 9 NOMAXVALUE NOCACHE NOCYCLE NOORDER,
|
|
KEY `x` (`x`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
drop table t;
|
|
create table t(x int generated by default as identity cache -2 maxvalue -1000 start with 0, key(x));
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`x` int(11) GENERATED BY DEFAULT AS IDENTITY START WITH 1 INCREMENT BY 1 NOMINVALUE NOMAXVALUE NOCACHE NOCYCLE NOORDER,
|
|
KEY `x` (`x`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
drop table t;
|
|
create table t(x int generated by default as identity nominvalue nomaxvalue nocache, key(x));
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`x` int(11) GENERATED BY DEFAULT AS IDENTITY START WITH 1 INCREMENT BY 1 NOMINVALUE NOMAXVALUE NOCACHE NOCYCLE NOORDER,
|
|
KEY `x` (`x`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
drop table t;
|
|
create table t(x int generated by default as identity
|
|
minvalue -9999999999999999999999999999
|
|
maxvalue 9999999999999999999999999999, key(x));
|
|
Warnings:
|
|
Warning 1916 Got overflow when converting '9999999999999999999999999999' to UNSIGNED INT. Value truncated
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`x` int(11) GENERATED BY DEFAULT AS IDENTITY START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 18446744073709551615 NOCACHE NOCYCLE NOORDER,
|
|
KEY `x` (`x`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
drop table t;
|
|
create table t(x int generated by default as identity
|
|
minvalue 9999999999999999999999999999
|
|
maxvalue -9999999999999999999999999999, key(x));
|
|
Warnings:
|
|
Warning 1916 Got overflow when converting '9999999999999999999999999999' to UNSIGNED INT. Value truncated
|
|
Warning 1916 Got overflow when converting '9999999999999999999999999999' to UNSIGNED INT. Value truncated
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`x` int(11) GENERATED BY DEFAULT AS IDENTITY START WITH 1 INCREMENT BY 1 MINVALUE 18446744073709551615 NOMAXVALUE NOCACHE NOCYCLE NOORDER,
|
|
KEY `x` (`x`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
drop table t;
|
|
create table t(x int generated by default as identity
|
|
# Some ulonglong with 64-th bit set
|
|
start with 0xd000000000000000, key(x));
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`x` int(11) GENERATED BY DEFAULT AS IDENTITY START WITH 14987979559889010688 INCREMENT BY 1 NOMINVALUE NOMAXVALUE NOCACHE NOCYCLE NOORDER,
|
|
KEY `x` (`x`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
drop table t;
|
|
# Compatibility options that are ignored
|
|
create table t(x int generated by default as identity
|
|
scale extend shard extend keep, key(x));
|
|
drop table t;
|
|
create table t(x int generated by default as identity
|
|
scale noextend shard noextend keep, key(x));
|
|
drop table t;
|
|
create table t(x int generated by default as identity
|
|
scale shard keep, key(x));
|
|
drop table t;
|
|
create table t(x int generated by default as identity
|
|
noscale noshard nokeep, key(x));
|
|
drop table t;
|
|
IDENTITY is created without specifying a key
|
|
create table t(x int generated by default as identity start with 10);
|
|
insert t() values(),(),();
|
|
select * from t;
|
|
x
|
|
10
|
|
11
|
|
12
|
|
# Begin testing mysqldump output + restore
|
|
# Create 'original table name - <table>_orig
|
|
SET @orig_table_name = CONCAT('test.t', '_orig');
|
|
# Rename original table
|
|
ALTER TABLE test.t RENAME to test.t_orig;
|
|
# Recreate table from mysqldump output
|
|
# Compare original and recreated tables
|
|
# Recreated table: test.t
|
|
# Original table: test.t_orig
|
|
include/diff_tables.inc [test.t, test.t_orig]
|
|
# Cleanup
|
|
DROP TABLE test.t, test.t_orig;
|