全部產品
Search
文件中心

PolarDB:建立唯讀分區

更新時間:Jul 06, 2024

您可以將分區和二級子分區設定為唯讀狀態,用於保護資料不受任何使用者或觸發器執行的DML操作的影響。

唯讀分區如下圖所示:唯讀分區

文法

  • 建立表時建立唯讀分區
    CREATE TABLE [IF NOT EXISTS] [schema.]table_name
        table_definition [READ {ONLY | WRITE}]
        partition_options;
    其中partition_options為:
    PARTITION BY
        { RANGE{(expr) | COLUMNS(column_list)} }
        [(partition_definition [, partition_definition] ...)]
    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]
  • 修改表時建立唯讀分區
    ALTER TABLE tbl_name
        [alter_option [, alter_option] ...]
        [partition_options]
    其中partition_options為:
    partition_options:
        partition_option [partition_option] ...
    partition_option為:
    partition_option: {
        ADD PARTITION (partition_definition)
    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]
    其中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
    }

參數

參數名稱參數說明
table_name表名稱。
expr分區欄位運算式。
column_list分區欄位列表,不支援運算式。
MAXVALUE最大值。
partition_name分區名稱,同一個表中不可重複。
engine_name儲存引擎名稱。
table_options表選項。
col_name列名。

注意事項

您不能修改唯讀分區的資料。如果嘗試修改唯讀分區的資料,系統會報錯ERROR HY000: Data in a read-only partition or subpartition cannot be modified.。這裡的修改操作不僅僅包括INSERT、UPDATE和DELETE操作,任何對唯讀分區資料結果產生變化的DDL操作都會被拒絕。

由於添加列不會修改原有的唯讀資料屬性,因此添加列是被允許的。如下:
ALTER TABLE [schema.]table_name ADD col_new varchar(100) DEFAULT 'i am not empty';

樣本

建立t1表時,指定t1表的所有分區為唯讀分區
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)
);
t1表新增唯讀分區
ALTER TABLE t1 ADD PARTITION (PARTITION p3 values LESS THAN (2030));
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0
新增唯讀分區後,可以發現新分區仍然為唯讀分區
SHOW CREATE TABLE t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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)
t1表建立混合屬性的分區
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
);

如何查看唯讀分區

目前只能通過SHOW CREATE命令查詢分區表的分區唯讀屬性,例如:
show create table t1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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)