Logo
广播表

广播表 #

语法描述类型
CREATE BROADCAST TABLE RULE tableName [, tableName] …创建广播表RDL
DROP BROADCAST TABLE RULE tableName [, tableName] …删除广播表RDL
SHOW BROADCAST TABLE RULES [FROM databaseName]查看广播表RQL
CREATE BROADCAST KEY_GENERATE_STRATEGY [IF NOT EXISTS] keyGenerateDefinition [, keyGenerateDefinition] …创建主键生成策略RDL
ALTER BROADCAST KEY_GENERATE_STRATEGY keyGenerateDefinition [, keyGenerateDefinition] …修改主键生成策略RDL
DROP BROADCAST KEY_GENERATE_STRATEGY [IF EXISTS] tableName [, tableName] …删除主键生成策略RDL
SHOW BROADCAST (KEY_GENERATE_STRATEGIESKEY_GENERATE_STRATEGY tableName ) [FROM databaseName]查看主键生成策略
keyGenerateDefinition:
    tableName (COLUMN=columnName,algorithmDefinition)
    
algorithmDefinition:
    TYPE(NAME=algorithmType [, PROPERTIES([propertiesDefinition])])
    
propertiesDefinition:
     PROPERTIES (properties?)
     
property:
    key=STRING_ EQ_ value=literal

参数说明

  • keyGenerateDefinition 主键生成策略定义
  • tableName 广播表名称
  • columnName 主键名称
  • algorithmDefinition 算法定义
  • algorithmType 主键生成算法类型
  • propertiesDefinition 算法参数

1. 创建广播表 #

CREATE BROADCAST TABLE RULE tableName [, tableName] ...

参数说明

名称数据类型说明
tableNameIDENTIFIER表名

示例

CREATE BROADCAST TABLE RULE t_a,t_b;

2. 删除广播表 #

DROP BROADCAST TABLE RULE tableName [, tableName] ...

参数说明

名称数据类型说明
tableNameIDENTIFIER表名

示例

DROP BROADCAST TABLE RULE t_a;

3. 查看广播表 #

SHOW BROADCAST TABLE RULES [FROM databaseName]

参数说明

名称数据类型说明
databaseNameIDENTIFIER逻辑库名称

示例

mysql> SHOW BROADCAST TABLE RULES;
+------------------------+
| broadcast_table        |
+------------------------+
| t_1                    |
| t_2                    |
+------------------------+
2 rows in set (0.00 sec)

输出说明

列说明
broadcast_table广播表名称

4. 创建主键生成策略 #

示例

CREATE BROADCAST KEY_GENERATE_STRATEGY t_address (COLUMN=address_id,TYPE(NAME="snowflake"));
    
CREATE BROADCAST KEY_GENERATE_STRATEGY IF NOT EXISTS t_address (COLUMN=address_id,TYPE(NAME="snowflake"));

5. 修改主键生成策略 #

示例

ALTER BROADCAST KEY_GENERATE_STRATEGY t_address (COLUMN=address_id,TYPE(NAME="snowflake"));

不能修改算法类型，可修改算法参数

6. 删除主键生成策略 #

示例

DROP BROADCAST KEY_GENERATE_STRATEGY t_address;

DROP BROADCAST KEY_GENERATE_STRATEGY IF EXISTS t_address;

7. 查看主键生成策略 #

示例

mysql> SHOW BROADCAST KEY_GENERATE_STRATEGIES;
+-----------+------------+-----------------------------+------------------------------+
| name      | column     | key_generate_algorithm_type | key_generate_algorithm_props |
+-----------+------------+-----------------------------+------------------------------+
| t_address | address_id | snowflake                   |                              |
+-----------+------------+-----------------------------+------------------------------+
1 row in set (0.01 sec)

mysql> SHOW BROADCAST KEY_GENERATE_STRATEGIES FROM sharding_db;
+-----------+------------+-----------------------------+------------------------------+
| name      | column     | key_generate_algorithm_type | key_generate_algorithm_props |
+-----------+------------+-----------------------------+------------------------------+
| t_address | address_id | snowflake                   |                              |
+-----------+------------+-----------------------------+------------------------------+
1 row in set (0.03 sec)

mysql> SHOW BROADCAST KEY_GENERATE_STRATEGY t_address FROM sharding_db;
+-----------+------------+-----------------------------+------------------------------+
| name      | column     | key_generate_algorithm_type | key_generate_algorithm_props |
+-----------+------------+-----------------------------+------------------------------+
| t_address | address_id | snowflake                   |                              |
+-----------+------------+-----------------------------+------------------------------+
1 row in set (0.01 sec)

输出说明

列说明
name策略名称
column主键列名称
key_generate_algorithm_type算法类型
key_generate_algorithm_props算法参数