博客
关于我
强烈建议你试试无所不能的chatGPT，快点击我
MySQL Study之（翻译）--分区键和唯一性索引及主键之关系
阅读量：5815 次
发布时间：2019-06-18

本文共 10223 字，大约阅读时间需要 34 分钟。

hot3.png

以下文档翻译自官方文档，水平有限，翻译不当之处，请大家指正微笑。

案例全部验证：

Partitioning Keys, Primary Keys, and Unique Keys

分区键、               主键、                     唯一性索引

This section discusses the relationship of partitioning keys with primary keys and unique keys. The rule governing this relationship can be expressed as follows: All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have.

这一节将讨论分区键和主键索引及唯一性索引之间的关系：

可以这样说：

在分区表上，用于分区表达式里的每一个字段都必须是唯一性索引的一部分。

In other words, every unique key on the table must use every column in the table's partitioning expression. (This also includes the table's primary key, since it is by definition a unique key. This particular case is discussed later in this section.) For example, each of the following table creation statements is invalid:

换句话说，表上的每一个唯一性索引必须用于分区表的表达式上（其中包括主键索引）。

例如：以下案例，建立分区表是无效的。

 

 

  1. mysql> CREATE TABLE t1 (  
  2.     ->     col1 INT NOT NULL,  
  3.     ->     col2 DATE NOT NULL,  
  4.     ->     col3 INT NOT NULL,  
  5.     ->     col4 INT NOT NULL,  
  6.     ->     UNIQUE KEY (col1, col2)  
  7.     -> )  
  8.     -> PARTITION BY HASH(col3)  
  9.     -> PARTITIONS 4;  

 

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

 

  1. mysql> CREATE TABLE t2 (  
  2.     ->     col1 INT NOT NULL,  
  3.     ->     col2 DATE NOT NULL,  
  4.     ->     col3 INT NOT NULL,  
  5.     ->     col4 INT NOT NULL,  
  6.     ->     UNIQUE KEY (col1),  
  7.     ->     UNIQUE KEY (col3)  
  8.     -> )  
  9.     -> PARTITION BY HASH(col1 + col3)  
  10.     -> PARTITIONS 4;  

 

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

In each case, the proposed table would have at least one unique key that does not include all columns used in the partitioning expression.

  以上案例：对于唯一性索引键，至少有一个字段不包含在分区表达式里

 

Each of the following statements is valid, and represents one way in which the corresponding invalid table creation statement could be made to work:

 

  1. mysql> CREATE TABLE t1 (  
  2.     ->     col1 INT NOT NULL,  
  3.     ->     col2 DATE NOT NULL,  
  4.     ->     col3 INT NOT NULL,  
  5.     ->     col4 INT NOT NULL,  
  6.     ->     UNIQUE KEY (col1, col2, col3)  
  7.     -> )  
  8.     -> PARTITION BY HASH(col3)  
  9.     -> PARTITIONS 4;  

 

Query OK, 0 rows affected (4.70 sec)

 

 

  1. mysql> CREATE TABLE t2 (  
  2.     ->     col1 INT NOT NULL,  
  3.     ->     col2 DATE NOT NULL,  
  4.     ->     col3 INT NOT NULL,  
  5.     ->     col4 INT NOT NULL,  
  6.     ->     UNIQUE KEY (col1, col3)  
  7.     -> )  
  8.     -> PARTITION BY HASH(col1 + col3)  
  9.     -> PARTITIONS 4;  

 

Query OK, 0 rows affected (2.93 sec)

This example shows the error produced in such cases:

 

 

  1. mysql> CREATE TABLE t3 (  
  2.     ->          col1 INT NOT NULL,  
  3.     ->          col2 DATE NOT NULL,  
  4.     ->          col3 INT NOT NULL,  
  5.     ->          col4 INT NOT NULL,  
  6.     ->          UNIQUE KEY (col1, col2),  
  7.     ->         UNIQUE KEY (col3)  
  8.     ->      )  
  9.     ->      PARTITION BY HASH(col1 + col3)  
  10.     ->     PARTITIONS 4;  

 

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

 

 

  1. mysql> CREATE TABLE t3a (  
  2.     ->          col1 INT NOT NULL,  
  3.     ->          col2 DATE NOT NULL,  
  4.     ->          col3 INT NOT NULL,  
  5.     ->          col4 INT NOT NULL,  
  6.     ->          UNIQUE KEY (col1, col2),  
  7.     ->         UNIQUE KEY (col3)  
  8.     ->      )  
  9.     ->      PARTITION BY HASH(col1 + col2)  
  10.     ->     PARTITIONS 4;  

 

ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function

 

 

  1. CREATE TABLE t3 (  
  2.          col1 INT NOT NULL,  
  3.          col2 DATE NOT NULL,  
  4.          col3 INT NOT NULL,  
  5.          col4 INT NOT NULL,  
  6.          UNIQUE KEY (col1, col2),  
  7.         UNIQUE KEY (col3)  
  8.      )  
  9.      PARTITION BY HASH(col1 + col2+col3 )  
  10.     PARTITIONS 4;  

 

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

 

The CREATE TABLE statement fails because both col1 and col3 are included in the proposed partitioning key, but neither of these columns is part of both of unique keys on the table. This shows one possible fix for the invalid table definition:

    以下cretate table语句失败了，是因为col1和col3不同时属于表里两个唯一性索引键。另外一个案例显示如何修复这个问题：

 

 

  1. mysql> CREATE TABLE t3 (  
  2.     ->          col1 INT NOT NULL,  
  3.     ->          col2 DATE NOT NULL,  
  4.     ->          col3 INT NOT NULL,  
  5.     ->          col4 INT NOT NULL,  
  6.     ->          UNIQUE KEY (col1, col2,col3),  
  7.     ->         UNIQUE KEY (col3)  
  8.     ->      )  
  9.     ->      PARTITION BY HASH(col1 + col2+col3 )  
  10.     ->     PARTITIONS 4;  

 

ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function

 

 

  1. mysql> CREATE TABLE t3 (  
  2.     ->          col1 INT NOT NULL,  
  3.     ->          col2 DATE NOT NULL,  
  4.     ->          col3 INT NOT NULL,  
  5.     ->          col4 INT NOT NULL,  
  6.     ->          UNIQUE KEY (col1, col2,col3),  
  7.     ->         UNIQUE KEY (col3)  
  8.     ->      )  
  9.     ->      PARTITION BY HASH(col3 )  
  10.     ->     PARTITIONS 4;  

 

Query OK, 0 rows affected (3.11 sec)

In this case, the proposed partitioning key col3 is part of both unique keys, and the table creation statement succeeds.

以上可以看出，col3同时属于两个唯一性索引的键，所以create table执行成功。

The following table cannot be partitioned at all, because there is no way to include in a partitioning key any columns that belong to both unique keys:

   如下所示，此分区表是无法建立的，因为没有一个分区键，可以同时属于两个唯一性索引的键。

 

 

  1. CREATE TABLE t4 (  
  2.     col1 INT NOT NULL,  
  3.     col2 INT NOT NULL,  
  4.     col3 INT NOT NULL,  
  5.     col4 INT NOT NULL,  
  6.     UNIQUE KEY (col1, col3),  
  7.     UNIQUE KEY (col2, col4)  
  8. );  

 

Since every primary key is by definition a unique key, this restriction also includes the table's primary key, if it has one. For example, the next two statements are invalid:

主键也属于唯一性索引，所以以上规则适合于primary key。

 

 

  1. mysql> CREATE TABLE t5 (  
  2.     ->     col1 INT NOT NULL,  
  3.     ->     col2 DATE NOT NULL,  
  4.     ->     col3 INT NOT NULL,  
  5.     ->     col4 INT NOT NULL,  
  6.     ->     PRIMARY KEY(col1, col2)  
  7.     -> )  
  8.     -> PARTITION BY HASH(col3)  
  9.     -> PARTITIONS 4;  

 

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

 

 

  1. mysql> CREATE TABLE t6 (  
  2.     ->     col1 INT NOT NULL,  
  3.     ->     col2 DATE NOT NULL,  
  4.     ->     col3 INT NOT NULL,  
  5.     ->     col4 INT NOT NULL,  
  6.     ->     PRIMARY KEY(col1, col3),  
  7.     ->     UNIQUE KEY(col2)  
  8.     -> )  
  9.     -> PARTITION BY HASH( YEAR(col2) )  
  10.     -> PARTITIONS 4;  

 

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

 

 

  1. mysql> CREATE TABLE t6 (  
  2.     ->          col1 INT NOT NULL,  
  3.     ->          col2 DATE NOT NULL,  
  4.     ->          col3 INT NOT NULL,  
  5.     ->        col4 INT NOT NULL,  
  6.     ->          PRIMARY KEY(col1,col2, col3),  
  7.     ->          UNIQUE KEY(col2)  
  8.     ->      )  
  9.     ->      PARTITION BY HASH( YEAR(col2) )  
  10.     ->      PARTITIONS 4;  

 

Query OK, 0 rows affected (2.88 sec)

 

 

  1. mysql> CREATE TABLE t6a(  
  2.     ->          col1 INT NOT NULL,  
  3.     ->          col2 DATE NOT NULL,  
  4.     ->          col3 INT NOT NULL,  
  5.     ->        col4 INT NOT NULL,  
  6.     ->          PRIMARY KEY(col1,col2, col3)  
  7.     ->      )  
  8.     ->      PARTITION BY HASH( YEAR(col2) )  
  9.     ->      PARTITIONS 4;  

 

Query OK, 0 rows affected (3.51 sec)

 

  1. mysql> CREATE TABLE t6b(  
  2.     ->          col1 INT NOT NULL,  
  3.     ->          col2 DATE NOT NULL,  
  4.     ->          col3 INT NOT NULL,  
  5.     ->        col4 INT NOT NULL,  
  6.     ->          PRIMARY KEY(col1,col2, col3)  
  7.     ->      )  
  8.     ->      PARTITION BY HASH( col3)  
  9.     ->      PARTITIONS 4;  

 

Query OK, 0 rows affected (4.26 sec)

In both cases, the primary key does not include all columns referenced in the partitioning expression. However, both of the next two statements are valid:

以上两个案例，分区表达式里的字段不包含所有的主键字段。

 

[sql]  

 

  1. mysql> CREATE TABLE t7 (  
  2.     ->     col1 INT NOT NULL,  
  3.     ->     col2 DATE NOT NULL,  
  4.     ->     col3 INT NOT NULL,  
  5.     ->     col4 INT NOT NULL,  
  6.     ->     PRIMARY KEY(col1, col2)  
  7.     -> )  
  8.     -> PARTITION BY HASH(col1 + YEAR(col2))  
  9.     -> PARTITIONS 4;  

 

Query OK, 0 rows affected (4.40 sec)

 

  1. mysql> CREATE TABLE t8 (  
  2.     ->     col1 INT NOT NULL,  
  3.     ->     col2 DATE NOT NULL,  
  4.     ->     col3 INT NOT NULL,  
  5.     ->     col4 INT NOT NULL,  
  6.     ->     PRIMARY KEY(col1, col2, col4),  
  7.     ->     UNIQUE KEY(col2, col1)  
  8.     -> )  
  9.     -> PARTITION BY HASH(col1 + YEAR(col2))  
  10.     -> PARTITIONS 4;  

 

Query OK, 0 rows affected (3.51 sec)

If a table has no unique keys—this includes having no primary key—then this restriction does not apply, and you may use any column or columns in the partitioning expression as long as the column type is compatible with the partitioning type.

对于没有建立主键或唯一性索引的表，可以将任何兼容分区表的字段放在分区表达式中。

For the same reason, you cannot later add a unique key to a partitioned table unless the key includes all columns used by the table's partitioning expression. Consider the partitioned table created as shown here:

基于同样的原因，你在分区表上添加唯一性索引时，必须符合以上规则。分区表表达式的键，必须包含在所有的唯一性索引中。

 

  1. mysql> CREATE TABLE t_no_pk (c1 INT, c2 INT)  
  2.     ->     PARTITION BY RANGE(c1) (  
  3.     ->         PARTITION p0 VALUES LESS THAN (10),  
  4.     ->         PARTITION p1 VALUES LESS THAN (20),  
  5.     ->         PARTITION p2 VALUES LESS THAN (30),  
  6.     ->         PARTITION p3 VALUES LESS THAN (40)  
  7.     ->     );  

 

Query OK, 0 rows affected (0.12 sec)

It is possible to add a primary key to t_no_pk using either of these ALTER TABLE statements:

#  possible PK

mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1);

Query OK, 0 rows affected (0.13 sec)

Records: 0  Duplicates: 0  Warnings: 0

# drop this PK

mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY;

Query OK, 0 rows affected (0.10 sec)

Records: 0  Duplicates: 0  Warnings: 0

#  use another possible PK

mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1, c2);

Query OK, 0 rows affected (0.12 sec)

Records: 0  Duplicates: 0  Warnings: 0

# drop this PK

mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY;

Query OK, 0 rows affected (0.09 sec)

Records: 0  Duplicates: 0  Warnings: 0

However, the next statement fails, because c1 is part of the partitioning key, but is not part of the proposed primary key:

 然而，以下语句会失败，因为分区键 c1，不属于primary key。

#  fails with error 1503

mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c2);

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

Since t_no_pk has only c1 in its partitioning expression, attempting to adding a unique key on c2 alone fails. However, you can add a unique key that uses both c1 and c2.

These rules also apply to existing nonpartitioned tables that you wish to partition using ALTER TABLE ... PARTITION BY. Consider a table np_pk created as shown here:

 

  1. mysql> CREATE TABLE np_pk (  
  2.     ->     id INT NOT NULL AUTO_INCREMENT,  
  3.     ->     name VARCHAR(50),  
  4.     ->     added DATE,  
  5.     ->     PRIMARY KEY (id)  
  6.     -> );  

 

Query OK, 0 rows affected (0.08 sec)

The following ALTER TABLE statement fails with an error, because the added column is not part of any unique key in the table:

以下，alter table语句将会失败，因为分区键，不包含于唯一性索引。

 

  1. mysql> ALTER TABLE np_pk  
  2.     ->     PARTITION BY HASH( TO_DAYS(added) )  
  3.     ->     PARTITIONS 4;  

 

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

However, this statement using the id column for the partitioning column is valid, as shown here:

以下语句将会成功：因为分区键id，包含于主键primary key

 

  1. mysql> ALTER TABLE np_pk  
  2.     ->     PARTITION BY HASH(id)  
  3.     ->     PARTITIONS 4;  

 

Query OK, 0 rows affected (0.11 sec)

Records: 0  Duplicates: 0  Warnings: 0

In the case of np_pk, the only column that may be used as part of a partitioning expression is id; if you wish to partition this table using any other column or columns in the partitioning expression, you must first modify the table, either by adding the desired column or columns to the primary key, or by dropping the primary key altogether.

  如以上案例，只有id字段可以作为分区表达式键，如果你还想使用其他的字段用于分区表达式中，你必须修改表结构，添加字段到你的主键里，或者删除主键。

转载于:https://my.oschina.net/dolphinboy/blog/853746

你可能感兴趣的文章
linux命令:ls
查看>>
Using RequireJS in AngularJS Applications
查看>>
hdu 2444(二分图最大匹配)
查看>>
shell编程笔记六：实现ll命令
查看>>
【SAP HANA】关于SAP HANA中带层次结构的计算视图Cacultation View创建、激活状况下在系统中生成对象的研究...
查看>>
[nodejs] nodejs开发个人博客（五）分配数据
查看>>
《Linux内核修炼之道》 之 高效学习Linux内核
查看>>
Java数据持久层框架 MyBatis之API学习九（SQL语句构建器详解）
查看>>
30分钟Git命令“从入门到放弃”
查看>>
nginx : TCP代理和负载均衡的stream模块
查看>>
MYSQL数据库间同步数据
查看>>
DevOps 前世今生 | mPaaS 线上直播 CodeHub #1 回顾
查看>>
iOS 解决UITabelView刷新闪动
查看>>
让前端小姐姐愉快地开发表单
查看>>
Dubbo笔记(四)
查看>>
Web前端JQuery入门实战案例
查看>>
java B2B2C Springboot电子商城系统- SSO单点登录之OAuth2.0 登出流程(3)
查看>>
USB 通信原理
查看>>
7zZip zip RAR iOS
查看>>
date命令的详细用法！
查看>>
喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！-- 愿君每日到此一游！
当前时间: 2024-12-23 02:15:16   当前IP: 18.225.98.39   联系邮箱:javaeecc@qq.com     Copyright © 2020 - 2022 baihongyu.com 京ICP备2021015314号-2
强烈建议你试试无所不能的CHAT-GPT，快点击我
强烈建议你试试无所不能的CHAT-GPT，快点击我