Rechercher dans le manuel MySQL

23.6.1 Partitioning Keys, Primary Keys, and Unique Keys

Inhaltsverzeichnis

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. 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;
  10.  
  11. CREATE TABLE t2 (
  12.     col1 INT NOT NULL,
  13.     col2 DATE NOT NULL,
  14.     col3 INT NOT NULL,
  15.     col4 INT NOT NULL,
  16.     UNIQUE KEY (col1),
  17.     UNIQUE KEY (col3)
  18. )
  19. PARTITION BY HASH(col1 + col3)
  20. PARTITIONS 4;

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. 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;
  10.  
  11. CREATE TABLE t2 (
  12.     col1 INT NOT NULL,
  13.     col2 DATE NOT NULL,
  14.     col3 INT NOT NULL,
  15.     col4 INT NOT NULL,
  16.     UNIQUE KEY (col1, col3)
  17. )
  18. PARTITION BY HASH(col1 + col3)
  19. PARTITIONS 4;

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;
  11. ERROR 1491 (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:

  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;
  11. Query OK, 0 rows affected (0.05 sec)

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

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:

  1. 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;
  10.  
  11. CREATE TABLE t6 (
  12.     col1 INT NOT NULL,
  13.     col2 DATE NOT NULL,
  14.     col3 INT NOT NULL,
  15.     col4 INT NOT NULL,
  16.     PRIMARY KEY(col1, col3),
  17.     UNIQUE KEY(col2)
  18. )
  19. PARTITION BY HASH( YEAR(col2) )
  20. PARTITIONS 4;

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:

  1. 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;
  10.  
  11. CREATE TABLE t8 (
  12.     col1 INT NOT NULL,
  13.     col2 DATE NOT NULL,
  14.     col3 INT NOT NULL,
  15.     col4 INT NOT NULL,
  16.     PRIMARY KEY(col1, col2, col4),
  17.     UNIQUE KEY(col2, col1)
  18. )
  19. PARTITION BY HASH(col1 + YEAR(col2))
  20. PARTITIONS 4;

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.     ->     );
  8. 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:

  1. #  possible PK
  2. mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1);
  3. Query OK, 0 rows affected (0.13 sec)
  4. Records: 0  Duplicates: 0  Warnings: 0
  5.  
  6. # drop this PK
  7. mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY;
  8. Query OK, 0 rows affected (0.10 sec)
  9. Records: 0  Duplicates: 0  Warnings: 0
  10.  
  11. #  use another possible PK
  12. mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1, c2);
  13. Query OK, 0 rows affected (0.12 sec)
  14. Records: 0  Duplicates: 0  Warnings: 0
  15.  
  16. # drop this PK
  17. mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY;
  18. Query OK, 0 rows affected (0.09 sec)
  19. 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:

  1. #  fails with error 1503
  2. mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c2);
  3. 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.     -> );
  7. 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:

  1. mysql> ALTER TABLE np_pk
  2.     ->     PARTITION BY HASH( TO_DAYS(added) )
  3.     ->     PARTITIONS 4;
  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:

  1. mysql> ALTER TABLE np_pk
  2.     ->     PARTITION BY HASH(id)
  3.     ->     PARTITIONS 4;
  4. Query OK, 0 rows affected (0.11 sec)
  5. 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.


Suchen Sie im MySQL-Handbuch

Deutsche Übersetzung

Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.

Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.

Vielen Dank im Voraus.

Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-partitioning-limitations-partitioning-keys-unique-keys.html

Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.

Referenzen

  1. Zeigen Sie - html-Dokument Sprache des Dokuments:en Manuel MySQL : https://dev.mysql.com/

Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.

Inhaltsverzeichnis Haut