mysql创建外键的表_MySQL表创建及外键

1. Employees数据库安装

(1)Employees数据库介绍

Employees用于学习和测试的数据库，大约160M

简单安装

mysql < employees.sql

2. 表(Table)

(1)表介绍

表是关系数据库的核心

表=关系

表是记录的集合

Mysql默认存储引擎都是基于行存储

(2)表是数据的集合

select * from table_name limit 1;

集合是无序的。从表中随机取出一条数据，结果无确定性。

只有通过order by 排序之后取出的数据才是确定的。

(3)创建表

官方文档表创建的语法

(1)临时表创建

create database temp;

use temp;

create temporary table temp_a(a int);

"root@localhost:mysql.sock [temp]>show create table temp_a\G;

1. row

Table: temp_a

Create Table: CREATE TEMPORARY TABLE temp_a (

a int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

1 row in set (0.00 sec)

默认存储引擎是InnoDB

insert into temp_a values(123);

select * from temp_a;

注意：临时表是session级别的。当前用户退出或者其他用户登录上来是看不到这张表的

如果临时表和普通表同名时,当前用户只能看到同名的临时表

(2)临时表的存储引擎

show variables like "default%tmp%";

"root@localhost:mysql.sock [temp]>show variables like "default%tmp%";

+----------------------------+--------+

| Variable_name | Value |

+----------------------------+--------+

| default_tmp_storage_engine | InnoDB |

+----------------------------+--------+

1 row in set (0.00 sec)

临时表的存储位置

system ls -l /tmp/

"root@localhost:mysql.sock [temp]>system ls -l /data/mysql_data/ | grep ib

-rw-r-----. 1 mysql mysql 744 Feb 24 15:48 ib_buffer_pool

-rw-r-----. 1 mysql mysql 12582912 Mar 12 14:02 ibdata1

-rw-r-----. 1 mysql mysql 4294967296 Mar 12 13:51 ib_logfile0

-rw-r-----. 1 mysql mysql 4294967296 Feb 24 15:43 ib_logfile1

-rw-r-----. 1 mysql mysql 12582912 Mar 12 14:04 ibtmp1

其中ibtmp1就是表结构对应的位置

查看临时表大小

show variables like "innodb_temp%";

(3)查看表结构

有3中方法

show create table temp_a \G;

desc temp_a\G;

show table status like "temp_a"\G;

(4)alter table

alter table temp_a add column b char(10);

alter table temp_a drop column b ;

注意：当表记录非常大时，alter table 会耗时，影响性能。

具体查看官网文档 ONLINE DDL

3. 外键索引

(1)外键的介绍

官方文档

CREATE TABLE product (

category INT NOT NULL,

id INT NOT NULL,

price DECIMAL,

PRIMARY KEY(category, id) )

ENGINE=INNODB;

CREATE TABLE customer (

id INT NOT NULL,

PRIMARY KEY (id)

) ENGINE=INNODB;

CREATE TABLE product_order (

no INT NOT NULL AUTO_INCREMENT,

product_category INT NOT NULL,

product_id INT NOT NULL,

customer_id INT NOT NULL,

PRIMARY KEY(no),

INDEX (product_category, product_id),

INDEX (customer_id),

FOREIGN KEY (product_category, product_id)

REFERENCES product(category, id)

ON UPDATE CASCADE ON DELETE RESTRICT,

FOREIGN KEY (customer_id) REFERENCES customer(id) ) ENGINE=INNODB;

(2)外键操作

表结构来自MySQL官方文档

create table parent(

id int not null,

primary key(id)

)engine=innodb;

create table child (

id int,

parent_id INT,

index par_ind (parent_id),

foreign key (parent_id)

references parent(id)

on delete cascade on update cascade

) engine=innodb;

(1)我们插入一条数据，id=1，parent_id=1

insert into child values(1,1);

报错信息如下：

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (temp.child, CONSTRAINT child_ibf
k_1 FOREIGN KEY (parent_id) REFERENCES parent (id) ON DELETE CASCADE ON UPDATE CASCADE)

insert into parent values(1);

insert into child values(1,1);

外键约束,可以让数据进行一致性更新,但是会有一定的性能损耗 ,线上业务使用不多。通常上述级联更新和删除都是由应用层业务逻辑进行判断并实现。