Cara membuat atau melihat partisi read-only - PolarDB - Alibaba Cloud Documentation Center

Partisi read-only melindungi data historis, arsip, atau data sensitif terkait kepatuhan dari modifikasi yang tidak disengaja. Anda dapat menandai partisi individual atau seluruh tabel sebagai read-only untuk memblokir operasi INSERT, UPDATE, dan DELETE—termasuk yang dipicu secara otomatis.

Gambar berikut mengilustrasikan partisi read-only. Read-only partition

Sintaksis

Tetapkan mode read-only tingkat tabel saat pembuatan

Terapkan READ ONLY atau READ WRITE pada tingkat tabel untuk menetapkan mode default bagi semua partisi. Partisi individual mewarisi pengaturan default ini kecuali ditimpa secara eksplisit.

CREATE TABLE [IF NOT EXISTS] [schema.]table_name
    table_definition [READ {ONLY | WRITE}]
    partition_options;

Sintaksis partition_options:

PARTITION BY
    { RANGE{(expr) | COLUMNS(column_list)} }
    [(partition_definition [, partition_definition] ...)]

Sintaksis partition_definition:

PARTITION partition_name
        [VALUES LESS THAN {expr | MAXVALUE}]
        [READ {ONLY | WRITE}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]

Tambahkan partisi read-only ke tabel yang sudah ada

Gunakan ALTER TABLE dengan ADD PARTITION untuk menambahkan partisi yang mewarisi mode read-only tingkat tabel.

ALTER TABLE tbl_name
    [alter_option [, alter_option] ...]
    [partition_options]

Sintaksis partition_options dan partition_option:

partition_options:
    partition_option [partition_option] ...

partition_option: {
    ADD PARTITION (partition_definition)

Sintaksis partition_definition:

PARTITION partition_name
        [VALUES LESS THAN {expr | MAXVALUE}]
        [READ {ONLY | WRITE}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]

Sintaksis alter_option:

alter_option: {
    table_options
  | ADD [COLUMN] col_name column_definition
            [FIRST | AFTER col_name]
  | ADD [COLUMN] (col_name column_definition,...)
  | ADD {INDEX | KEY} [index_name]
            [index_type] (key_part,...) [index_option] ...
  | ADD {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name]
            (key_part,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY
            [index_type] (key_part,...)
            [index_option] ...
  | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
            [index_name] [index_type] (key_part,...)
            [index_option] ...
  | ADD [CONSTRAINT [symbol]] FOREIGN KEY
            [index_name] (col_name,...)
            reference_definition
  | ADD [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
  | DROP {CHECK | CONSTRAINT} symbol
  | ALTER {CHECK | CONSTRAINT} symbol [NOT] ENFORCED
  | ALGORITHM [=] {DEFAULT | INSTANT | INPLACE | COPY}
  | ALTER [COLUMN] col_name {
            SET DEFAULT {literal | (expr)}
          | SET {VISIBLE | INVISIBLE}
          | DROP DEFAULT
        }
  | ALTER INDEX index_name {VISIBLE | INVISIBLE}
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
            [FIRST | AFTER col_name]
  | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | {DISABLE | ENABLE} KEYS
  | {DISCARD | IMPORT} TABLESPACE
  | DROP [COLUMN] col_name
  | DROP {INDEX | KEY} index_name
  | DROP PRIMARY KEY
  | DROP FOREIGN KEY fk_symbol
  | FORCE
  | LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}
  | MODIFY [COLUMN] col_name column_definition
            [FIRST | AFTER col_name]
  | ORDER BY col_name [, col_name] ...
  | RENAME COLUMN old_col_name TO new_col_name
  | RENAME {INDEX | KEY} old_index_name TO new_index_name
  | RENAME [TO | AS] new_tbl_name
  | {WITHOUT | WITH} VALIDATION
}

Parameter

Parameter	Deskripsi
`table_name`	Nama tabel.
`expr`	Ekspresi kolom kunci partisi.
`column_list`	Daftar kolom kunci partisi. Ekspresi tidak didukung.
`MAXVALUE`	Nilai maksimum dalam partisi.
`partition_name`	Nama partisi. Harus unik dalam tabel.
`engine_name`	Nama mesin penyimpanan.
`table_options`	Opsi tabel.
`col_name`	Nama kolom.

Catatan penggunaan

Operasi yang diizinkan dan diblokir

Operasi	Diizinkan pada partisi read-only
`SELECT`	Ya
`INSERT`	Tidak
`UPDATE`	Tidak
`DELETE`	Tidak
DDL yang mengubah data	Tidak
`ALTER TABLE ... ADD COLUMN`	Ya — menambahkan kolom tidak mengubah data yang sudah ada

Ketika operasi yang diblokir dicoba, error berikut akan dikembalikan:

ERROR HY000: Data in a read-only partition or subpartition cannot be modified.

Contoh operasi DDL yang diizinkan pada tabel dengan partisi read-only:

ALTER TABLE [schema.]table_name ADD col_new varchar(100) DEFAULT 'i am not empty';

Contoh

Buat tabel dengan semua partisi read-only

Terapkan READ ONLY pada tingkat tabel. Semua partisi mewarisi mode tersebut.

CREATE TABLE t1 (
  id INT,
  year_col INT
) READ ONLY
PARTITION BY RANGE (year_col) (
  PARTITION p0 VALUES LESS THAN (2001),
  PARTITION p1 VALUES LESS THAN (2010),
  PARTITION p2 VALUES LESS THAN (2020)
);

Tambahkan partisi read-only ke tabel yang sudah ada

Partisi baru yang ditambahkan ke tabel READ ONLY secara otomatis mewarisi mode tingkat tabel.

ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2030));

Output yang diharapkan:

Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verifikasi bahwa p3 mewarisi mode READ ONLY tingkat tabel:

SHOW CREATE TABLE t1;

Output yang diharapkan:

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `year_col` int(11) DEFAULT NULL
) /*!99990 800020201 READ ONLY */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (`year_col`)
(PARTITION p0 VALUES LESS THAN (2001) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2020) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2030) ENGINE = InnoDB) */ |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Penanda READ ONLY dalam blok komentar /*!99990 800020201 ... */ mengonfirmasi bahwa mode tingkat tabel aktif dan keempat partisi mewarisinya.

Buat tabel dengan campuran partisi read-only dan read/write

Tetapkan tabel ke READ WRITE dan timpa partisi individual dengan READ ONLY.

CREATE TABLE t1 (
    id INT,
    year_col INT
) READ WRITE
PARTITION BY RANGE (year_col) (
    PARTITION p0 VALUES LESS THAN (2001) READ ONLY,
    PARTITION p1 VALUES LESS THAN (2010),
    PARTITION p2 VALUES LESS THAN (2020) READ ONLY
);

Verifikasi partisi mana saja yang bersifat read-only:

SHOW CREATE TABLE t1;

Output yang diharapkan:

+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `year_col` int(11) DEFAULT NULL
) /*!99990 800020201 READ WRITE */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (`year_col`)
(PARTITION p0 VALUES LESS THAN (2001) */ /*!99990 800020201  READ ONLY */
/*!50100  ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2020) */ /*!99990 800020201  READ ONLY */
/*!50100  ENGINE = InnoDB) */ |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Partisi p0 dan p2 menampilkan READ ONLY dalam blok komentarnya masing-masing, sedangkan p1 tidak memiliki penimpaan dan menggunakan default READ WRITE.