MySQL的表分区

相关链接:
 
MySQL手册:http://dev.mysql.com/doc/refman/5.1/zh/partitioning.html
 
MySQL分表与分区的区别:http://www.isoji.org/articles/mysql%E5%88%86%E8%A1%A8%E5%88%86%E5%8C%BA%E7%9A%84%E5%8C%BA%E5%88%AB%E5%92%8C%E8%81%94%E7%B3%BB-71588-1.html
 
 

一、什么是表分区
通俗地讲表分区是将一大表,根据条件分割成若干个小表。mysql5.1开始支持数据表分区了。
如:某用户表的记录超过了600万条,那么就可以根据入库日期将表分区,也可以根据所在地将表分区。当然也可根据其他的条件分区。

 

二、为什么要对表进行分区
为了改善大型表以及具有各种访问模式的表的可伸缩性,可管理性和提高数据库效率。

分区的一些优点包括:
      1)、与单个磁盘或文件系统分区相比,可以存储更多的数据
      2)、 对于那些已经失去保存意义的数据,通常可以通过删除与那些数据有关的分区,很容易地删除那些数据。相反地,在某些情况下,添加新数据的过程又可以通过为那 些新数据专门增加一个新的分区,来很方便地实现。通常和分区有关的其他优点包括下面列出的这些。MySQL分区中的这些功能目前还没有实现,但是在我们的 优先级列表中,具有高的优先级;我们希望在5.1的生产版本中,能包括这些功能。
      3)、一些查询可以得到极大的优化,这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内,这样在查找时就不用查找其他剩余的分 区。因为分区可以在创建了分区表后进行修改,所以在第一次配置分区方案时还不曾这么做时,可以重新组织数据,来提高那些常用查询的效率。
      4)、涉及到例如SUM()和COUNT()这样聚合函数的查询,可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id;”。通过“并行”,这意味着该查询可以在每个分区上同时进行,最终结果只需通过总计所有分区得到的结果。
      5)、通过跨多个磁盘来分散数据查询,来获得更大的查询吞吐量。


三、分区类型

 

· RANGE分区:基于属于一个给定连续区间的列值,把多行分配给分区。
· LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
· HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
· KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

  • RANGE分区

       基于属于一个给定连续区间的列值,把多行分配给分区。

       这些区间要连续且不能相互重叠,使用VALUES LESS THAN操作符来进行定义。以下是实例。

 

Sql代码
  1. CREATE TABLE employees (  
  2.     id INT NOT NULL,  
  3.     fname VARCHAR(30),  
  4.     lname VARCHAR(30),  
  5.     hired DATE NOT NULL DEFAULT ‘1970-01-01’,  
  6.     separated DATE NOT NULL DEFAULT ‘9999-12-31’,  
  7.     job_code INT NOT NULL,  
  8.     store_id INT NOT NULL  
  9. )  
  10.   
  11. partition BY RANGE (store_id) (  
  12.     partition p0 VALUES LESS THAN (6),  
  13.     partition p1 VALUES LESS THAN (11),  
  14.     partition p2 VALUES LESS THAN (16),  
  15.     partition p3 VALUES LESS THAN (21)  
  16. );   

       按照这种分区方案,在商店1到5工作的雇员相对应的所有行被保存在分区P0中,商店6到10的雇员保存在P1中,依次类推。注意,每个分区都是按顺序进行定义,从最低到最高。这是PARTITION BY RANGE 语法的要求;在这点上,它类似于C或Java中的“switch … case”语句。
       对于包含数据(72, ‘Michael’, ‘Widenius’, ‘1998-06-25’, NULL, 13)的一个新行,可以很容易地确定它将插入到p2分区中,但是如果增加了一个编号为第21的商店,将会发生什么呢?在这种方案下,由于没有规则把 store_id大于20的商店包含在内,服务器将不知道把该行保存在何处,将会导致错误。 要避免这种错误,可以通过在CREATE TABLE语句中使用一个“catchall” VALUES LESS THAN子句,该子句提供给所有大于明确指定的最高值的值:

Sql代码
  1. CREATE TABLE employees (  
  2.     id INT NOT NULL,  
  3.     fname VARCHAR(30),  
  4.     lname VARCHAR(30),  
  5.     hired DATE NOT NULL DEFAULT ‘1970-01-01’,  
  6.     separated DATE NOT NULL DEFAULT ‘9999-12-31’,  
  7.     job_code INT NOT NULL,  
  8.     store_id INT NOT NULL  
  9. )  
  10.   
  11. PARTITION BY RANGE (store_id) (  
  12.     PARTITION p0 VALUES LESS THAN (6),  
  13.     PARTITION p1 VALUES LESS THAN (11),  
  14.     PARTITION p2 VALUES LESS THAN (16),  
  15.     PARTITION p3 VALUES LESS THAN MAXVALUE  
  16. );  

    MAXVALUE 表示最大的可能的整数值。现在,store_id 列值大于或等于16(定义了的最高值)的所有行都将保存在分区p3中。在将来的某个时候,当商店数已经增长到25, 30, 或更多 ,可以使用ALTER TABLE语句为商店21-25, 26-30,等等增加新的分区。
     在几乎一样的结构中,你还可以基于雇员的工作代码来分割表,也就是说,基于job_code 列值的连续区间。例如——假定2位数字的工作代码用来表示普通(店内的)工人,三个数字代码表示办公室和支持人员,四个数字代码表示管理层,你可以使用下 面的语句创建该分区表:

Sql代码
  1. CREATE TABLE employees (  
  2.     id INT NOT NULL,  
  3.     fname VARCHAR(30),  
  4.     lname VARCHAR(30),  
  5.     hired DATE NOT NULL DEFAULT ‘1970-01-01’,  
  6.     separated DATE NOT NULL DEFAULT ‘9999-12-31’,  
  7.     job_code INT NOT NULL,  
  8.     store_id INT NOT NULL  
  9. )  
  10.   
  11. PARTITION BY RANGE (job_code) (  
  12.     PARTITION p0 VALUES LESS THAN (100),  
  13.     PARTITION p1 VALUES LESS THAN (1000),  
  14.     PARTITION p2 VALUES LESS THAN (10000)  
  15. );  

 
在这个例子中, 店内工人相关的所有行将保存在分区p0中,办公室和支持人员相关的所有行保存在分区p1中,管理层相关的所有行保存在分区p2中。
       在VALUES LESS THAN 子句中使用一个表达式也是可能的。这里最值得注意的限制是MySQL 必须能够计算表达式的返回值作为LESS THAN (<)比较的一部分;因此,表达式的值不能为NULL 。由于这个原因,雇员表的hired, separated, job_code,和store_id列已经被定义为非空(NOT NULL)。
       除了可以根据商店编号分割表数据外,你还可以使用一个基于两个DATE (日期)中的一个的表达式来分割表数据。例如,假定你想基于每个雇员离开公司的年份来分割表,也就是说,YEAR(separated)的值。实现这种分 区模式的CREATE TABLE 语句的一个例子如下所示:

Sql代码
  1. CREATE TABLE employees (  
  2.     id INT NOT NULL,  
  3.     fname VARCHAR(30),  
  4.     lname VARCHAR(30),  
  5.     hired DATE NOT NULL DEFAULT ‘1970-01-01’,  
  6.     separated DATE NOT NULL DEFAULT ‘9999-12-31’,  
  7.     job_code INT,  
  8.     store_id INT  
  9. )  
  10.   
  11. PARTITION BY RANGE (YEAR(separated)) (  
  12.     PARTITION p0 VALUES LESS THAN (1991),  
  13.     PARTITION p1 VALUES LESS THAN (1996),  
  14.     PARTITION p2 VALUES LESS THAN (2001),  
  15.     PARTITION p3 VALUES LESS THAN MAXVALUE  
  16. );  

 在这个方案中,在1991年前雇佣的所有雇员的记录保存在分区p0中,1991年到1995年期间雇佣的所有雇员的记录保存在分区p1中, 1996年到2000年期间雇佣的所有雇员的记录保存在分区p2中,2000年后雇佣的所有工人的信息保存在p3中。

RANGE分区在如下场合特别有用:
      1)、 当需要删除一个分区上的“旧的”数据时,只删除分区即可。 如果你使用上面最近的那个例子给出的分区方案,你只需简单地使用 “ALTER TABLE employees DROP PARTITION p0;”来删除所有在1991年前就已经停止工作的雇员相对应的所有行。对于有大量行的表,这比运行一个如“DELETE FROM employees WHERE YEAR (separated) <= 1990;”这样的一个DELETE查询要有效得多。
      2)、想要使用一个包含有日期或时间值,或包含有从一些其他级数开始增长的值的列。
      3)、经常运行直接依赖于用于分割表的列的查询。例如,当执行一个如“SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id;”这样的查询时,MySQL可以很迅速地确定只有分区p2需要扫描,这是因为余下的分区不可能包含有符合该WHERE子句的任何记录。

注释:这种优化还没有在MySQL 5.1源程序中启用,但是,有关工作正在进行中。

  • LIST分区

      类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

      LIST分区通过使用“PARTITION BY LIST(expr)”来实现,其中“expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式,然后通过“VALUES IN (value_list)”的方式来定义每个分区,其中“value_list”是一个通过逗号分隔的整数列表
注释:在MySQL 5.1中,当使用LIST分区时,有可能只能匹配整数列表。

Sql代码
  1. CREATE TABLE employees (  
  2.     id INT NOT NULL,  
  3.     fname VARCHAR(30),  
  4.     lname VARCHAR(30),  
  5.     hired DATE NOT NULL DEFAULT ‘1970-01-01’,  
  6.     separated DATE NOT NULL DEFAULT ‘9999-12-31’,  
  7.     job_code INT,  
  8.     store_id INT  
  9. );  

 
假定有20个音像店,分布在4个有经销权的地区,如下表所示:

====================
地区      商店ID 号

————————————

北区      3, 5, 6, 9, 17
东区      1, 2, 10, 11, 19, 20
西区      4, 12, 13, 14, 18
中心区   7, 8, 15, 16

====================
要按照属于同一个地区商店的行保存在同一个分区中的方式来分割表,可以使用下面的“CREATE TABLE”语句:

Sql代码
  1. CREATE TABLE employees (  
  2.     id INT NOT NULL,  
  3.     fname VARCHAR(30),  
  4.     lname VARCHAR(30),  
  5.     hired DATE NOT NULL DEFAULT ‘1970-01-01’,  
  6.     separated DATE NOT NULL DEFAULT ‘9999-12-31’,  
  7.     job_code INT,  
  8.     store_id INT  
  9. )  
  10.   
  11. PARTITION BY LIST(store_id)  
  12.     PARTITION pNorth VALUES IN (3,5,6,9,17),  
  13.     PARTITION pEast VALUES IN (1,2,10,11,19,20),  
  14.     PARTITION pWest VALUES IN (4,12,13,14,18),  
  15.     PARTITION pCentral VALUES IN (7,8,15,16)  
  16. );  

 

这使得在表中增加或删除指定地区的雇员记录变得容易起来。例如,假定西区的所有音像店都卖给了其他公司。那么与在西区音像店工作雇员相关的所有记录 (行)可以使用查询“ALTER TABLE employees DROP PARTITION pWest;”来进行删除,它与具有同样作用的DELETE (删除)查询“DELETE query DELETE FROM employees WHERE store_id IN (4,12,13,14,18);”比起来,要有效得多。
【要点】:如果试图插入列值(或分区表达式的返回值)不在分区值列表中的一行时,那么“INSERT”查询将失败并报错。例如,假定LIST分区的采用上面的方案,下面的查询将失败:

Sql代码
  1. INSERT INTO employees VALUES(224, ‘Linus’, ‘Torvalds’, ‘2002-05-01’, ‘2004-10-12’, 42, 21);  

 
这是因为“store_id”列值21不能在用于定义分区pNorth, pEast, pWest,或pCentral的值列表中找到。要重点注意的是,LIST分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。

LIST分区除了能和RANGE分区结合起来生成一个复合的子分区,与HASH和KEY分区结合起来生成复合的子分区也是可能的。

  • HASH分区         

       基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

      要使用HASH分区来分割一个表,要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句,其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL 整型的一列的名字。此外,你很可能需要在后面再添加一个“PARTITIONS num”子句,其中num 是一个非负的整数,它表示表将要被分割成分区的数量。

Sql代码
  1. CREATE TABLE employees (  
  2.     id INT NOT NULL,  
  3.     fname VARCHAR(30),  
  4.     lname VARCHAR(30),  
  5.     hired DATE NOT NULL DEFAULT ‘1970-01-01’,  
  6.     separated DATE NOT NULL DEFAULT ‘9999-12-31’,  
  7.     job_code INT,  
  8.     store_id INT  
  9. )  
  10. PARTITION BY HASH(store_id)  
  11. PARTITIONS 4;  

 如果没有包括一个PARTITIONS子句,那么分区的数量将默认为1。 例外: 对于NDB Cluster(簇)表,默认的分区数量将与簇数据节点的数量相同,

这种修正可能是考虑任何MAX_ROWS 设置,以便确保所有的行都能合适地插入到分区中。

  • LINER HASH

MySQL还支持线性哈希功能,它与常规哈希的区别在于,线性哈希功能使用的一个线性的2的幂(powers-of-two)运算法则,而常规 哈希使用的是求哈希函数值的模数。
线性哈希分区和常规哈希分区在语法上的唯一区别在于,在“PARTITION BY” 子句中添加“LINEAR”关键字。

Sql代码
  1. CREATE TABLE employees (  
  2.     id INT NOT NULL,  
  3.     fname VARCHAR(30),  
  4.     lname VARCHAR(30),  
  5.     hired DATE NOT NULL DEFAULT ‘1970-01-01’,  
  6.     separated DATE NOT NULL DEFAULT ‘9999-12-31’,  
  7.     job_code INT,  
  8.     store_id INT  
  9. )  
  10. PARTITION BY LINEAR HASH(YEAR(hired))  
  11. PARTITIONS 4;  

 

假设一个表达式expr, 当使用线性哈希功能时,记录将要保存到的分区是num 个分区中的分区N,其中N是根据下面的算法得到:
1.    找到下一个大于num.的、2的幂,我们把这个值称为V ,它可以通过下面的公式得到:
2.    V = POWER(2, CEILING(LOG(2, num)))
(例如,假定num是13。那么LOG(2,13)就是3.7004397181411。 CEILING(3.7004397181411)就是4,则V = POWER(2,4), 即等于16)。
3.    设置 N = F(column_list) & (V – 1).
4.    当 N >= num:
·         设置 V = CEIL(V / 2)
·         设置 N = N & (V – 1)
例如,假设表t1,使用线性哈希分区且有4个分区,是通过下面的语句创建的:
CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
    PARTITION BY LINEAR HASH( YEAR(col3) )
    PARTITIONS 6;
现在假设要插入两行记录到表t1中,其中一条记录col3列值为’2003-04-14’,另一条记录col3列值为’1998-10-19’。第一条记录将要保存到的分区确定如下:
V = POWER(2, CEILING(LOG(2,7))) = 8
N = YEAR(‘2003-04-14’) & (8 – 1)
   = 2003 & 7
   = 3
(3 >= 6 为假(FALSE): 记录将被保存到#3号分区中)
第二条记录将要保存到的分区序号计算如下:
V = 8
N = YEAR(‘1998-10-19’) & (8-1)
  = 1998 & 7
  = 6
(6 >= 4 为真(TRUE): 还需要附加的步骤)
N = 6 & CEILING(5 / 2)
  = 6 & 3
  = 2
 
(2 >= 4 为假(FALSE): 记录将被保存到#2分区中)
按照线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷,有利于处理含有极其大量(1000吉)数据的表。它的缺点在于,与使用

常规HASH分区得到的数据分布相比,各个分区间数据的分布不大可能均衡。

  • KSY分区

类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

Sql代码
  1. CREATE TABLE tk (  
  2.     col1 INT NOT NULL,  
  3.     col2 CHAR(5),  
  4.     col3 DATE  
  5. )  
  6. PARTITION BY LINEAR KEY (col1)  
  7. PARTITIONS 3;  

 
在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用,分区的编号是通过2的幂(powers-of-two)算法得到,而不是通过模数算法。

MySQL Proxy – 官方MySQL连接池和代理工具

包括了查询分析(query analysis & query filtering),同时它可以根据分析结果,决定转发方向. 完成了一主多从,或一主(一备主)多从中,写和读操作的完美区分.为replication提供了更多的应用空间,同时还有负载均衡,失败转移等许多高级 功能。至此,我们可以得出结论,它的主要作用就是为我们的MySQL数据库保驾护航

MySQL外键的使用

像MySQL这样的关系型数据库管理系统,它们的基础是在数据库的表之间创建关系的能力。通过方便地在不同表中建立记录到记录的联系,RDBMS可以利用不同的方法分析数据,同时保持数据库以系统的方式、最小的冗余进行组织。

 
 

简单描述:
这些关系基本上依靠外键进行管理,在关系中所有表中具有相同含义的字段作为公共部分来连接不同表中的记录。外键可以是一对一的,一个表的记录只能与另一个表的一条记录连接,或者是一对多的,一个表的记录与另一个表的多条记录连接。

 

MySQL中“键”和“索引”的定义相同, 所以外键和主键一样也是索引的一种。不同的是MySQL会自动为所有表的主键进行索引,但是外键字段必须由用户进行明确的索引。这和一些封建思想比较沉重的家庭是一样的,外来的孩子(儿媳妇,倒插门女婿)一般都是不受重视的。

 
 

低俗示例:

表间一对一关系示例:

有两张表,第一张表是记录公司有多少人,都有谁,也就是员工编号及员工姓名这些基本表。另一张表记录每个月发给用户多少工资,所谓工资表是也。

 

但是工资表里面不能以员工姓名为主键,同样要通过员工id,因为员工的姓名是可能重复的啊。部门经理叫张三,小弟也叫张三,那这俩张三的工资能一样吗?并且员工表里面的每个人都有工资,否则谁也不给你干活,且一个人只能有一份工资,否则老板也不同意了。所以员工表和工资表是通过员工id进行关联的一对一关系

 

不过我们要有一个好的价值观,我们上班不能为了钱,我们是为了学知识,学文化,为早日实现四个现代化(别问我是啥,也别问我到底实现没有)而努力奋斗。所以在工资表里如果没有你也不要乱喊。嗯。

 
/*
建立员工表
*/
create table employees (
id int(5) not null auto_increment ,
name varchar(8) not null,
primary key (id)
)
typeinnodb;

/*
建立工资表
*/

create table payroll(
id int(5) not null,
emp_id int(5) not null,
name varchar(8) not null,
payroll float(4,2) not null,
primary key(id),
index emp_id (emp_id),
foreign key (emp_id) references employees (id)
)
type = innodb;

 

表间一对多关系示例:

有两个表,一个是贪官表,有贪官的id和名字。另有一张贪官情妇表,注意一个贪官不一定只有一个情妇,其有个二三四五奶是很正常的,所以在贪官表里面的一条数据,对应情妇表里可能就有多条记录,这是通过贪官id进行关联的一对多关系

 
 

参照完整性:

当外键与另一个表的字段有关系,而且这种关系是惟一时,这个系统就称为处于参照完整性的状态。也就是说,如果一个字段在所有的表中只出现一次,而且每个表的这个字段的变化都会影响其他表,这就是存在参照完整性。

术语理解上可能不太方便,其实就是说要在有外键的表中保持所有数据的一致性。比如说“张三”离职了,在员工表里面肯定没有这个人了,可是如果在工资表里面还存在这个孩子,那么老大就会很生气的

另外,比如说一个县官,因为一些小政绩,由县官变成了知府,那么他的那些情妇的地位也要调整一下,最起码得从县官二奶改为知府二奶,否则这位二奶也是不会同意的

 
 

MySQL的外键只能在InnoDB表中使用:

当今主流数据库都会自动考虑参照完整性的问题。当你更新或删除数据时,其会把相关联的表中数据也都给你变过来。比如县官张三改名为王二麻子,其情妇的称号就会自动改为王二麻子的情妇。嗯。

MySQL对此一直持观望态度,它允许使用外键,但是为了完整性检验的目的,在除了InnoDB表类型之外的所有表类型中都忽略了这个功能。这可能有些怪异,实际上却非常正常:对于数据库的所有外键的每次插入、更新和删除后,进行完整性检查是一个耗费时间和资源的过程,它可能影响性能,特别是当处理复杂的或者是缠绕的连接树时。因而,用户可以在表的基础上,选择适合于特定需求的最好结合。

所以,如果需要更好的性能,并且不需要完整性检查,可以选择使用MyISAM表类型,如果想要在MySQL中根据参照完整性来建立表并且希望在此基础上保持良好的性能,最好选择表结构为innoDB类型。

 
 

MySQL创建外键语法:

创建外键的语法是这样的:FOREIGN KEY (当前表的字段名)… REFERENCES 参照表 (参照表的字段名)

foreign key (emp_id) references employees (id); 的意思就是说当前表的emp_id字段是以employees的id字段为外键的。

 
 

注意事项:

  • 一旦建立外键,MySQL只允许向当前表中加入外键表中已有的数据列。比如说贪官表里有“王二麻子”,那么在情妇表只才能有“王二麻子的情妇”。也就是说只有确认一个人是贪官了,才能把其情妇信息列入此表中,否则是不行滴。
  • 关系中的所有表必须是innoDB表,在非InnoDB表中,MySQL将会忽略FOREIGN KEY…REFERENCES修饰符。
  • 用于外键关系的字段必须在所有的参照表中进行明确地索引,InnoDB不能自动地创建索引。
  • 在外键关系中,字段的数据类型必须相似,这对于大小和符号都必须匹配的整数类型尤其重要。
  • 即使表存在外键约束,MySQL还允许我们删除表,并且不会产生错误(即使这样做可能会破坏更早创建的外键)

删除外键方法:

long long ago,人们只能通过删除表来删除外键。不过现在MySQL(在4.0.13及更高版本中)提供了一种从表中删除外键比较缓和的方法,缓和与否不太清楚,但是至少不再那么无耻。

 

ALTER TABLE table-name DROP FOREIGN KEY key-id;

 

这里有一个概念,这个外键的id是啥玩意?我们可以通过SHOW CREATE TABLE 命令来获得key-id的值。日后我们详细讨论这些内容,大家可以自行演示。

/*
显示建表结构语句,key-id为payroll_ibfk_1
*/
show create table payroll \G
/*
*************************** 1. row ***************************
       Table: payroll
Create Table: CREATE TABLE payroll (
  id int(5) NOT NULL,
  emp_id int(5) NOT NULL,
  name varchar(8) NOT NULL,
  payroll float(4,2) NOT NULL,
  PRIMARY KEY (id),
  KEY emp_id (emp_id),
  CONSTRAINT payroll_ibfk_1 FOREIGN KEY (emp_id) REFERENCES employees (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

*/

 
 
 

自动键更新和删除:

外键可以保证新插入的记录的完整性。但是,如果在REFERENCES从句中从已命名的表删除记录会怎样?在使用同样的值作为外键的辅助表中会发生什么?

很明显,那些记录也应该被删除,否则在数据库中就会有很多无意义的孤立记录。MySQL可能通过向FOREIGN KEY…REFERENCES 修饰符添加一个ON DELETE或ON UPDATE子句简化任务,它告诉了数据库在这种情况如何处理孤立任务。

 

关键字 含义
CASCADE 删除包含与已删除键值有参照关系的所有记录
SET NULL 修改包含与已删除键值有参照关系的所有记录,使用NULL值替换(只能用于已标记为NOT NULL的字段)
RESTRICT 拒绝删除要求,直到使用删除键值的辅助表被手工删除,并且没有参照时(这是默认设置,也是最安全的设置)
NO ACTION 啥也不做

请注意,通过 ON UPDATE 和ON DELETE规则,设置MySQL能够实现自动操作时,如果键的关系没有设置好,可能会导致严重的数据破坏。例如,如果一系列的表通过外键关系和ON DELETE CASCADE 规则连接时,任意一个主表的变化都会导致甚至只和原始删除有一些将要联系的记录在没有警告的情况下被删除。所以,我们在操作之前还是要检查这些规则的,操作之后还要再次检查。