IT数码 购物 网址 头条 软件 日历 阅读 图书馆
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
图片批量下载器
↓批量下载图片,美女图库↓
图片自动播放器
↓图片自动播放器↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
开发: C++知识库 Java知识库 JavaScript Python PHP知识库 人工智能 区块链 大数据 移动开发 嵌入式 开发工具 数据结构与算法 开发测试 游戏开发 网络协议 系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑 笔记本 显卡 显示器 固态硬盘 硬盘 耳机 手机 iphone vivo oppo 小米 华为 单反 装机 图拉丁
 
   -> 大数据 -> MySQL8.0学习记录17 -Create Table -> 正文阅读

[大数据]MySQL8.0学习记录17 -Create Table

这里主要列举一些以前没有注意过的地方:

不可见列

不可见列通常对查询是隐藏的,但如果显式引用,可以被访问。

create table t_visible(
	a int,
	b int invisible
);

select * from t_visible ;
a|
-+

可以通过show columns from t_visible;查看Extra列来判断是否是隐藏列。通过select * from information_schema.COLUMNS c where EXTRA = ‘INVISIBLE’;可以查看所以得隐藏列。

创建临时表

CREATE TABLE会导致隐式提交,但是创建临时表并不会。

  • 要创建一个临时表,必须有CREATE TEMPORARY TABLES权限。在一个会话创建了一个临时表后,服务器不再对该表进行权限检查。即使当前用户没有创建临时表的权限,会话也可以操作其临时表。比如,高权限的存储过程创建临时表之后,当前用户依然可以访问临时表。

CREATE TABLE LIKE

CREATE TABLE LIKE创建一个基于原始表的定义的空表,包括在原始表中定义的任何列属性和索引。

  • 会保留生成列信息
  • 会保留表达式的的默认值
  • 会保留了原表的CHECK约束,只是所有的约束名称都被重新生成
  • 如果原表是临时表,除非加关键字TEMPORARY ,否则新表不会是临时表

CREATE TABLE … SELECT

我们常见的SELECT语句创建表是这样的:

CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl;

其实还可以先指定一部分列:

create table t_create_t0(
	a int
);
insert into t_create_t0 values(1);

create table t_create_t1(b int default 5) as select a from t_create_t0;

select * from t_create_t1;
b|a|
-+-+
5|1|

从MySQL 8.0.19开始,在CREATE TABLE … SELECT语句中可以使用VALUES语句作为一张表,这是一个很有用的功能。比如,有下面一张表t_book表:

select * from t_book tb ;
ID|NAME  |
--+------+
 0|Python|
 2|Java  |

给定一组用户名,希望将每个用户与全部的book数据关联并输出:

select t.*,tb.name from (values ROW('u1','上海'), row('u2','北京')) as t(name,address) 
join t_book tb
name|address|name  |
----+-------+------+
u2  |北京     |Python|
u1  |上海     |Python|
u2  |北京     |Java  |
u1  |上海     |Java  |

CREATE TABLE … SELECT并不会自动创建索引,但可以在CREATE TABLE 部分指定列属性,也可以创建索引。
CREATE TABLE … SELECT也不会保留生成列信息,但可以保留原表的表达式默认值。

CREATE TABLE … SELECT不会保留AUTO_INCREMENT ,但是会保留 NULL (NOT NULL) 、 CHARACTER SET, COLLATION, COMMENT以及DEFAULT属性。

在MySQL8.0.21前,表的创建与数据的插入是两部分。所以数据插入失败新表依然会创建,但是新的版本是一个事务,数据插入失败,创建的表也会被回滚。

外键

外键约束名称必须是数据库内唯一的,如果不指定的话,那么会自动生成。
外键的使用有很多约束:

  • 外键的创建需要在父表上有REFERENCES权限
  • 父表和子表必须使用相同的存储引擎,并且它们不能被定义为临时表
  • 外键和引用键中对应的列必须有类似的数据类型,字符串的长度可以不同,但是字符集和校对规则必须是相同的。
  • 外键和引用列可以是同一张表上的,这个在应该是在自引用查询中有用
  • 外键列和引用列都必须有索引。在建立外键的时候,外键不存在索引的话,会创建索引。如果先建立外键,然后再建索引,这时候会悄悄删掉外键自己建的索引:
    create table t_parent(
    	id int primary key,
    	name varchar(100) ,
    	index idx_p_name (name)
    );
    
    create table t_child(
    	id int primary key,
    	name varchar(100),
    	p_name varchar(100),
    	FOREIGN KEY(p_name) REFERENCES t_parent(name) on DELETE CASCADE
    );
    
    这时候看下t_child索引:
    Table  |Non_unique|Key_name|Seq_in_index|Column_name|Collation
    -------+----------+--------+------------+-----------+---------
    t_child|         0|PRIMARY |           1|id         |A        
    t_child|         1|p_name  |           1|p_name     |A        
    
    然后新增一个索引再看看, 可以看到上面p_name索引居然消失了!
    alter table t_child  add index nfk_p_name(p_name);
    
    Table  |Non_unique|Key_name  |Seq_in_index|Column_name
    -------+----------+----------+------------+-----------
    t_child|         0|PRIMARY   |           1|id         
    t_child|         1|nfk_p_name|           1|p_name     
    
  • 外键列上的前缀索引还不支持,所以太大varchar不能用来做外键,还有BLOB以及TEXT
  • InnoDB目前不支持具有用户定义的分区的表的外键
  • 一个外键约束不能引用一个虚拟生成的列

外键的参照动作:

  • CASCADE:从父表中删除或更新行,并自动删除或更新子表中的匹配行。ON DELETE CASCADE和ON UPDATE CASCADE都被支持。
  • SET NULL:从父表中删除或更新记录,并将子表中的外键列设置为NULL。
  • RESTRICT。拒绝对父表进行删除或更新操作。指定RESTRICT(或NO ACTION)与省略ON DELETE或ON UPDATE子句相同。
  • NO ACTION:标准SQL的一个关键字。在MySQL中,相当于RESTRICT。
  • SET DEFAULT:可以被MySQL解析器识别,但InnoDB和NDB都拒绝包含ON DELETE SET DEFAULT或ON UPDATE SET DEFAULT子句的表定义。也就是不能用。

外键检查是由foreign_key_checks变量控制的,这个默认是启用的。但是有时候是需要禁用的,禁用的好处有:

  • 正常被引用的表不能被删除,禁用检查以后可以删
  • mysqldump中自动包含禁用外键检查,可以按照任意顺序加载表,还能加快导入操作的速度

外键禁用后,仍然存在禁止的操作:

  • 重新创建一个以前被放弃的表(表的定义不符合引用该表的外键约束)会返回一个错误
  • 如果改变表会导致外键定义不正确,那么会出错
  • 不能删除外键约束要求的索引,除非先删除外键约束

需要注意的是,禁用外键检查后,删除父表是被允许的,而且子表的外键约束自动删除。

查询已有外键的方式:

// 查询列是外键列:
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
       FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
       WHERE REFERENCED_TABLE_SCHEMA IS NOT NULL;
       
// 查询外键,会给出引用表与列
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;

//查询表的外键列
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS

检查约束

在MySQL 8.0.16以前,检查约束的语法比较简单:

CHECK (expr)

MySQL8.0.16之后, 语法更加丰富:

[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]

约束的名称(symbol)不指定的话,数据库会自动生成一个,名称的最大长度为64个字符,区分大小写但不区分重音。

约束条件(expr)需要指定为一个布尔表达式,表达式的值必须是TRUE或UNKNOWN(对NULL来说)。

默认约束具有ENFORCED属性,既强制执行。可以改为NOT ENFORCED,不强制执行约束。

约束可以是针对列的,也可以是针对表的。列的约束出现在一个列的定义中,并且只能参考该列。表约束不出现在列的定义中,它可以引用任何表的一个或多个列,允许向前引用出现在表定义后面的列。

之前说约束的名称是数据库内唯一的,但是有个例外,一个TEMPORARY表会隐藏一个同名的非TEMPORARY表,所以它也可以有相同的CHECK约束名称。这里提到TEMPORARY表可以重名以前不知道,验证一下:


create table t_not_tmp(
	id int primary key,
	c1 int CONSTRAINT `c1_positive` CHECK ((`c1` > 0))
);

create temporary table t_not_tmp(
	i1 int ,
	i2 int CONSTRAINT `c1_positive` CHECK ((`i2` > 0))
);

select * from t_not_tmp;
i1|i2|
--+--+

CHECK条件表达式必须遵守的规则:

  • 非生成的和生成的列都是允许的,具有AUTO_INCREMENT属性的列不行
  • 表达式可以使用字面量、已经结果确定的内建函数和操作。像connection_id(), current_user(), now()结果不确定的不行
  • 不允许使用变量(系统变量、用户定义的变量和存储的程序局部变量)
  • 不允许使用子查询
  • 不允许使用自己创建的函数
  • 不允许使用存储过程
  • CHECK约束与外键的动作(ON UPDATE, ON DELETE)互斥,不能同时使用

对INSERT IGNORE, UPDATE IGNORE, LOAD DATA … IGNORE, 和 LOAD XML … IGNORE 语句,如果约束检查失败,则会出现警告,并跳过违规行的插入或更新。没有IGNORE的语句,约束检查失败的时候会报错。

需要注意:如果约束表达式中,出现与声明的列类型不同的数据类型,那么根据通常的MySQL类型转换规则,会发生对声明类型的隐式转化。

创建语句的一些隐含变化

  • 主键或主键的一部分一定会被声明为 NOT NULL
  • ENUM和SET成员值的尾部空格会被自动删除
  • MySQL将其他SQL数据库供应商使用的某些数据类型映射为MySQL类型,比如BOOL类型,在MySQL中是TINYINT
  • USING 限定索引类型的时候,如果当前数据库引擎不支持,那么会使用可以使用的索引类型
  • 非严格模式下,长度规格大于65535的VARCHAR列会被转换为TEXT,而长度规格大于65535的VARBINARY列会被转换为BLOB
  • 字符数据类型指定CHARACTER SET二进制属性会导致列被创建为相应的二进制数据类型。CHAR变成BINARY,VARCHAR变成VARBINARY,TEXT变成BLOB。

生成列

可以依据一个现有列的表达式,添加一个新的列,比如:

create table t_v_c(
	first_name varchar(20),
	last_name varchar(20),
	full_name varchar(40) as (concat(first_name,' ', last_name)),
	dateOfBirth datetime,
	birthday varchar(5) as (date_format(dateOfBirth, '%m-%d')) STORED
);
insert into t_v_c(first_name,last_name,dateOfBirth) values('A','B','2020-01-02');
select * from t_v_c;
first_name|last_name|full_name|dateOfBirth        |birthday|
----------+---------+---------+-------------------+--------+
A         |B        |A B      |2020-01-02 00:00:00|01-02   |

生成列完整的定义语法:

col_name data_type [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED] [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']
  • GENERATED ALWAYS,这个是可以省略的,但是建议带上,更加直观表示是生成列
  • VIRTUAL | STORED,VIRTUAL 表示不需要额外存储,而是在读取的时候计算(在任何BEFORE触发器之后),可以在虚拟列上建立二级索引;STORED表示需要存储下来,在行插入或者更新的时候计算,可以建立索引。默认是VIRTUAL
  • NOT NULL | NULL, 字面意义上就可以理解,即便是VIRTUAL 列NOT NULL, 如果插入时最终的表达式为null也会报错
  • UNIQUE [KEY]:建立唯一索引,VIRTUAL 也可以,如果在插入失败时,仍然会报错
  • [PRIMARY] KEY:建立主键索引,只有STORED的生成列才可以

生成列的表达式要求:

  • 字面量、结果确定的内建函数和操作符是可以的
  • 自定义的函数式不行的
  • 存储过程和函数参数是不允许的
  • 不允许使用变量
  • 不允许使用子查询
  • 生成列可以引用任何位置的普通列,但是不能引用在其后面定义的其他生成列,也就是说引用生成列时顺序是很重要的
  • 生成列不能使用AUTO_INCREMENT属性,可以理解
  • 具有AUTO_INCREMENT列也不能被生成列引用,这个没想明白,先记下来

外键约束不能引用一个虚拟生成的列,虚拟列也不能添加外键约束,但是STORED生成列可以加上外键约束引用其他列,而且其上的外键约束不能使用CASCADE、SET NULL或SET DEFAULT作为ON UPDATE参考动作,也不能使用SET NULL或SET DEFAULT作为ON DELETE参考动作。

生成列有哪些作用呢:

  • VIRTUAL 生成列可以作为一种简化和统一查询的方式,比如将复杂的查询条件包装成生成列,这样就可以在多个查询中使用,模块化查询条件
  • STORED生成列可以用于复杂的条件,尤其是这些条件在查询的时候的代价很高的话,相等于提前缓存结果
  • 生成的列可以模拟功能索引,使用一个生成的列来定义一个函数表达式并为其建立索引。 比如JSON数据类型就可以通过生成列建立索引
  • 如果生成的列是有索引的,优化器会识别与列定义相匹配的查询表达式,并在查询执行过程中适当地使用列的索引,即使查询没有直接引用列的名称。举个例子如果生成列c int as (d+1) STORED 上有建立索引,那么只要查询条件中匹配到了生成列的表达式(比如 where d+1 > 9),就可以使用到索引。

InnoDB支持虚拟生成列的二级索引,也叫虚拟索引。虽然虚拟列的值是在查询的时候被计算的,但是当一个二级索引在虚拟生成列上被创建时,生成的列值在索引的记录中被具体化。

在虚拟列上使用二级索引时,由于在INSERT和UPDATE操作过程中在二级索引记录中物化虚拟列值时进行的计算,需要考虑额外的写入成本。但是,文档说在虚拟列上添加或删除一个二级索引是一个 in-place 操作,这意味着应该会比较快。

自动生成的不可见主键

MySQL 8.0.30开始,如果创建表的时候没有指定主键,且开启了GIPK (generated invisible primary key)模式的话,mySQL会自动添加一个不可见的主键。
GIPK的开关由sql_generate_invisible_primary_key 变量控制,生成的不可见列将是这样的:

 `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  PRIMARY KEY (`my_row_id`)

my_row_id是固定的,所以该模式下,尽量不要给一个没有主键的表使用这个名称作为列名。

当GIPK模式生效的时候,除了在可见和不可见之间切换之外,不能改变生成的主键。

由于是不可见列,所以select * 查询不到,需要明确指明这一列才可以查出来。

  大数据 最新文章
实现Kafka至少消费一次
亚马逊云科技:还在苦于ETL?Zero ETL的时代
初探MapReduce
【SpringBoot框架篇】32.基于注解+redis实现
Elasticsearch:如何减少 Elasticsearch 集
Go redis操作
Redis面试题
专题五 Redis高并发场景
基于GBase8s和Calcite的多数据源查询
Redis——底层数据结构原理
上一篇文章      下一篇文章      查看所有文章
加:2022-06-29 19:08:56  更:2022-06-29 19:10:40 
 
开发: C++知识库 Java知识库 JavaScript Python PHP知识库 人工智能 区块链 大数据 移动开发 嵌入式 开发工具 数据结构与算法 开发测试 游戏开发 网络协议 系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑 笔记本 显卡 显示器 固态硬盘 硬盘 耳机 手机 iphone vivo oppo 小米 华为 单反 装机 图拉丁

360图书馆 购物 三丰科技 阅读网 日历 万年历 2024年3日历 -2024/3/29 1:32:44-

图片自动播放器
↓图片自动播放器↓
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
图片批量下载器
↓批量下载图片,美女图库↓
  网站联系: qq:121756557 email:121756557@qq.com  IT数码