关于 OceanBase 数据库字符集和字符序的说明

OceanBase 数据库支持多种字符集和字符序，允许你根据实际需要存储各种语言文字，并控制字符串的比较和排序规则。

• 字符集（Character Set）： 定义了字符的编码规则，决定了数据库能存储哪些字符。例如，utf8mb4 可以存储包括表情符号在内的绝大多数 Unicode 字符。

• 字符序（Collation）： 定义了同一字符集内字符的比较、排序规则。例如，在 utf8mb4 字符集下。

  • utf8mb4_bin 会严格区分大小写。

  • utf8mb4_general_ci 则不区分大小写（ci 表示 Case Insensitive）。

本文主要介绍了 OceanBase 数据库字符集和字符序的基本知识和常见用法。

详细说明

1. 查看当前版本的租户支持的字符集和字符序。

   -- MySQL 模式和 Oracle 模式下均支持
   show charset; -- 查看当前版本租户支持的所有字符集
   show collation; -- 查看当前版本租户支持的所有字符序
   

   例如在 OceanBase 数据库 V4.2.5 版本中，支持如下种类的字符集和字符序：

    obclient> show charset;
    +--------------+-----------------------+-------------------------+--------+
    | CHARSET      | DESCRIPTION           | DEFAULT COLLATION       | MAXLEN |
    +--------------+-----------------------+-------------------------+--------+
    | binary       | Binary pseudo charset | binary                  |      1 |
    | utf8mb4      | UTF-8 Unicode         | utf8mb4_general_ci      |      4 |
    | gbk          | GBK charset           | gbk_chinese_ci          |      2 |
    | utf16        | UTF-16 Unicode        | utf16_general_ci        |      4 |
    | gb18030      | GB18030 charset       | gb18030_chinese_ci      |      4 |
    | latin1       | cp1252 West European  | latin1_swedish_ci       |      1 |
    | gb18030_2022 | GB18030-2022 charset  | gb18030_2022_chinese_ci |      4 |
    | ascii        | US ASCII              | ascii_general_ci        |      1 |
    | tis620       | TIS620 Thai           | tis620_thai_ci          |      1 |
    | utf16le      | UTF-16LE Unicode      | utf16le_general_ci      |      4 |
    | sjis         | SJIS                  | sjis_japanese_ci        |      2 |
    | big5         | BIG5                  | big5_chinese_ci         |      2 |
    | hkscs        | HKSCS                 | hkscs_bin               |      2 |
    | hkscs31      | HKSCS-ISO UNICODE 31  | hkscs31_bin             |      2 |
    | dec8         | DEC West European     | dec8_swedish_ci         |      1 |
    +--------------+-----------------------+-------------------------+--------+
    15 rows in set (0.004 sec)
   
    obclient> show collation;
    +----------------------------+--------------+------+---------+----------+---------+
    | COLLATION                  | CHARSET      | ID   | DEFAULT | COMPILED | SORTLEN |
    +----------------------------+--------------+------+---------+----------+---------+
    | utf8mb4_general_ci         | utf8mb4      |   45 | Yes     | Yes      |       1 |
    | utf8mb4_bin                | utf8mb4      |   46 | NULL    | Yes      |       1 |
    | binary                     | binary       |   63 | Yes     | Yes      |       1 |
    | gbk_chinese_ci             | gbk          |   28 | Yes     | Yes      |       1 |
    | gbk_bin                    | gbk          |   87 | NULL    | Yes      |       1 |
    | utf16_general_ci           | utf16        |   54 | Yes     | Yes      |       1 |
    | utf16_bin                  | utf16        |   55 | NULL    | Yes      |       1 |
    | gb18030_chinese_ci         | gb18030      |  248 | Yes     | Yes      |       2 |
    | gb18030_bin                | gb18030      |  249 | NULL    | Yes      |       1 |
    | latin1_swedish_ci          | latin1       |    8 | Yes     | Yes      |       1 |
    | latin1_german1_ci          | latin1       |    5 | NULL    | Yes      |       1 |
    | latin1_danish_ci           | latin1       |   15 | NULL    | Yes      |       1 |
    | latin1_german2_ci          | latin1       |   31 | NULL    | Yes      |       1 |
    | latin1_general_ci          | latin1       |   48 | NULL    | Yes      |       1 |
    | latin1_general_cs          | latin1       |   49 | NULL    | Yes      |       1 |
    | latin1_spanish_ci          | latin1       |   94 | NULL    | Yes      |       1 |
    | latin1_bin                 | latin1       |   47 | NULL    | Yes      |       1 |
    | gb18030_2022_bin           | gb18030_2022 |  216 | NULL    | Yes      |       1 |
    | gb18030_2022_chinese_ci    | gb18030_2022 |  217 | Yes     | Yes      |       1 |
    | gb18030_2022_chinese_cs    | gb18030_2022 |  218 | NULL    | Yes      |       1 |
    | gb18030_2022_radical_ci    | gb18030_2022 |  219 | NULL    | Yes      |       1 |
    | gb18030_2022_radical_cs    | gb18030_2022 |  220 | NULL    | Yes      |       1 |
    | gb18030_2022_stroke_ci     | gb18030_2022 |  221 | NULL    | Yes      |       1 |
    | gb18030_2022_stroke_cs     | gb18030_2022 |  222 | NULL    | Yes      |       1 |
    | ascii_general_ci           | ascii        |   11 | Yes     | Yes      |       1 |
    | ascii_bin                  | ascii        |   65 | NULL    | Yes      |       1 |
    | tis620_thai_ci             | tis620       |   18 | Yes     | Yes      |       1 |
    | tis620_bin                 | tis620       |   89 | NULL    | Yes      |       1 |
    | utf16le_general_ci         | utf16le      |   56 | Yes     | Yes      |       1 |
    | utf16le_bin                | utf16le      |   62 | NULL    | Yes      |       1 |
    | sjis_japanese_ci           | sjis         |   13 | Yes     | Yes      |       1 |
    | sjis_bin                   | sjis         |   88 | NULL    | Yes      |       1 |
    | big5_chinese_ci            | big5         |    1 | Yes     | Yes      |       1 |
    | big5_bin                   | big5         |   84 | NULL    | Yes      |       1 |
    | hkscs_bin                  | hkscs        |  152 | Yes     | Yes      |       1 |
    | hkscs31_bin                | hkscs31      |  153 | Yes     | Yes      |       1 |
    | utf16_unicode_ci           | utf16        |  101 | NULL    | Yes      |       8 |
    | utf16_icelandic_ci         | utf16        |  102 | NULL    | Yes      |       8 |
    | utf16_latvian_ci           | utf16        |  103 | NULL    | Yes      |       8 |
    | utf16_romanian_ci          | utf16        |  104 | NULL    | Yes      |       8 |
    | utf16_slovenian_ci         | utf16        |  105 | NULL    | Yes      |       8 |
    | utf16_polish_ci            | utf16        |  106 | NULL    | Yes      |       8 |
    | utf16_estonian_ci          | utf16        |  107 | NULL    | Yes      |       8 |
    | utf16_spanish_ci           | utf16        |  108 | NULL    | Yes      |       8 |
    | utf16_swedish_ci           | utf16        |  109 | NULL    | Yes      |       8 |
    | utf16_turkish_ci           | utf16        |  110 | NULL    | Yes      |       8 |
    | utf16_czech_ci             | utf16        |  111 | NULL    | Yes      |       8 |
    | utf16_danish_ci            | utf16        |  112 | NULL    | Yes      |       8 |
    | utf16_lithuanian_ci        | utf16        |  113 | NULL    | Yes      |       8 |
    | utf16_slovak_ci            | utf16        |  114 | NULL    | Yes      |       8 |
    | utf16_spanish2_ci          | utf16        |  115 | NULL    | Yes      |       8 |
    | utf16_roman_ci             | utf16        |  116 | NULL    | Yes      |       8 |
    | utf16_persian_ci           | utf16        |  117 | NULL    | Yes      |       8 |
    | utf16_esperanto_ci         | utf16        |  118 | NULL    | Yes      |       8 |
    | utf16_hungarian_ci         | utf16        |  119 | NULL    | Yes      |       8 |
    | utf16_sinhala_ci           | utf16        |  120 | NULL    | Yes      |       8 |
    | utf16_german2_ci           | utf16        |  121 | NULL    | Yes      |       8 |
    | utf16_croatian_ci          | utf16        |  122 | NULL    | Yes      |       8 |
    | utf16_unicode_520_ci       | utf16        |  123 | NULL    | Yes      |       8 |
    | utf16_vietnamese_ci        | utf16        |  124 | NULL    | Yes      |       8 |
    | utf8mb4_unicode_ci         | utf8mb4      |  224 | NULL    | Yes      |       8 |
    | utf8mb4_icelandic_ci       | utf8mb4      |  225 | NULL    | Yes      |       8 |
    | utf8mb4_latvian_ci         | utf8mb4      |  226 | NULL    | Yes      |       8 |
    | utf8mb4_romanian_ci        | utf8mb4      |  227 | NULL    | Yes      |       8 |
    | utf8mb4_slovenian_ci       | utf8mb4      |  228 | NULL    | Yes      |       8 |
    | utf8mb4_polish_ci          | utf8mb4      |  229 | NULL    | Yes      |       8 |
    | utf8mb4_estonian_ci        | utf8mb4      |  230 | NULL    | Yes      |       8 |
    | utf8mb4_spanish_ci         | utf8mb4      |  231 | NULL    | Yes      |       8 |
    | utf8mb4_swedish_ci         | utf8mb4      |  232 | NULL    | Yes      |       8 |
    | utf8mb4_turkish_ci         | utf8mb4      |  233 | NULL    | Yes      |       8 |
    | utf8mb4_czech_ci           | utf8mb4      |  234 | NULL    | Yes      |       8 |
    | utf8mb4_danish_ci          | utf8mb4      |  235 | NULL    | Yes      |       8 |
    | utf8mb4_lithuanian_ci      | utf8mb4      |  236 | NULL    | Yes      |       8 |
    | utf8mb4_slovak_ci          | utf8mb4      |  237 | NULL    | Yes      |       8 |
    | utf8mb4_spanish2_ci        | utf8mb4      |  238 | NULL    | Yes      |       8 |
    | utf8mb4_roman_ci           | utf8mb4      |  239 | NULL    | Yes      |       8 |
    | utf8mb4_persian_ci         | utf8mb4      |  240 | NULL    | Yes      |       8 |
    | utf8mb4_esperanto_ci       | utf8mb4      |  241 | NULL    | Yes      |       8 |
    | utf8mb4_hungarian_ci       | utf8mb4      |  242 | NULL    | Yes      |       8 |
    | utf8mb4_sinhala_ci         | utf8mb4      |  243 | NULL    | Yes      |       8 |
    | utf8mb4_german2_ci         | utf8mb4      |  244 | NULL    | Yes      |       8 |
    | utf8mb4_croatian_ci        | utf8mb4      |  245 | NULL    | Yes      |       8 |
    | utf8mb4_unicode_520_ci     | utf8mb4      |  246 | NULL    | Yes      |       8 |
    | utf8mb4_vietnamese_ci      | utf8mb4      |  247 | NULL    | Yes      |       8 |
    | dec8_swedish_ci            | dec8         |    3 | Yes     | Yes      |       8 |
    | dec8_bin                   | dec8         |   69 | NULL    | Yes      |       8 |
    | utf8mb4_0900_ai_ci         | utf8mb4      |  255 | NULL    | Yes      |       0 |
    | utf8mb4_de_pb_0900_ai_ci   | utf8mb4      |  256 | NULL    | Yes      |       0 |
    | utf8mb4_is_0900_ai_ci      | utf8mb4      |  257 | NULL    | Yes      |       0 |
    | utf8mb4_lv_0900_ai_ci      | utf8mb4      |  258 | NULL    | Yes      |       0 |
    | utf8mb4_ro_0900_ai_ci      | utf8mb4      |  259 | NULL    | Yes      |       0 |
    | utf8mb4_sl_0900_ai_ci      | utf8mb4      |  260 | NULL    | Yes      |       0 |
    | utf8mb4_pl_0900_ai_ci      | utf8mb4      |  261 | NULL    | Yes      |       0 |
    | utf8mb4_et_0900_ai_ci      | utf8mb4      |  262 | NULL    | Yes      |       0 |
    | utf8mb4_es_0900_ai_ci      | utf8mb4      |  263 | NULL    | Yes      |       0 |
    | utf8mb4_sv_0900_ai_ci      | utf8mb4      |  264 | NULL    | Yes      |       0 |
    | utf8mb4_tr_0900_ai_ci      | utf8mb4      |  265 | NULL    | Yes      |       0 |
    | utf8mb4_cs_0900_ai_ci      | utf8mb4      |  266 | NULL    | Yes      |       0 |
    | utf8mb4_da_0900_ai_ci      | utf8mb4      |  267 | NULL    | Yes      |       0 |
    | utf8mb4_lt_0900_ai_ci      | utf8mb4      |  268 | NULL    | Yes      |       0 |
    | utf8mb4_sk_0900_ai_ci      | utf8mb4      |  269 | NULL    | Yes      |       0 |
    | utf8mb4_es_trad_0900_ai_ci | utf8mb4      |  270 | NULL    | Yes      |       0 |
    | utf8mb4_la_0900_ai_ci      | utf8mb4      |  271 | NULL    | Yes      |       0 |
    | utf8mb4_eo_0900_ai_ci      | utf8mb4      |  273 | NULL    | Yes      |       0 |
    | utf8mb4_hu_0900_ai_ci      | utf8mb4      |  274 | NULL    | Yes      |       0 |
    | utf8mb4_hr_0900_ai_ci      | utf8mb4      |  275 | NULL    | Yes      |       0 |
    | utf8mb4_vi_0900_ai_ci      | utf8mb4      |  277 | NULL    | Yes      |       0 |
    | utf8mb4_0900_as_cs         | utf8mb4      |  278 | NULL    | Yes      |       0 |
    | utf8mb4_de_pb_0900_as_cs   | utf8mb4      |  279 | NULL    | Yes      |       0 |
    | utf8mb4_is_0900_as_cs      | utf8mb4      |  280 | NULL    | Yes      |       0 |
    | utf8mb4_lv_0900_as_cs      | utf8mb4      |  281 | NULL    | Yes      |       0 |
    | utf8mb4_ro_0900_as_cs      | utf8mb4      |  282 | NULL    | Yes      |       0 |
    | utf8mb4_sl_0900_as_cs      | utf8mb4      |  283 | NULL    | Yes      |       0 |
    | utf8mb4_pl_0900_as_cs      | utf8mb4      |  284 | NULL    | Yes      |       0 |
    | utf8mb4_et_0900_as_cs      | utf8mb4      |  285 | NULL    | Yes      |       0 |
    | utf8mb4_es_0900_as_cs      | utf8mb4      |  286 | NULL    | Yes      |       0 |
    | utf8mb4_sv_0900_as_cs      | utf8mb4      |  287 | NULL    | Yes      |       0 |
    | utf8mb4_tr_0900_as_cs      | utf8mb4      |  288 | NULL    | Yes      |       0 |
    | utf8mb4_cs_0900_as_cs      | utf8mb4      |  289 | NULL    | Yes      |       0 |
    | utf8mb4_da_0900_as_cs      | utf8mb4      |  290 | NULL    | Yes      |       0 |
    | utf8mb4_lt_0900_as_cs      | utf8mb4      |  291 | NULL    | Yes      |       0 |
    | utf8mb4_sk_0900_as_cs      | utf8mb4      |  292 | NULL    | Yes      |       0 |
    | utf8mb4_es_trad_0900_as_cs | utf8mb4      |  293 | NULL    | Yes      |       0 |
    | utf8mb4_la_0900_as_cs      | utf8mb4      |  294 | NULL    | Yes      |       0 |
    | utf8mb4_eo_0900_as_cs      | utf8mb4      |  296 | NULL    | Yes      |       0 |
    | utf8mb4_hu_0900_as_cs      | utf8mb4      |  297 | NULL    | Yes      |       0 |
    | utf8mb4_hr_0900_as_cs      | utf8mb4      |  298 | NULL    | Yes      |       0 |
    | utf8mb4_vi_0900_as_cs      | utf8mb4      |  300 | NULL    | Yes      |       0 |
    | utf8mb4_ja_0900_as_cs      | utf8mb4      |  303 | NULL    | Yes      |       0 |
    | utf8mb4_ja_0900_as_cs_ks   | utf8mb4      |  304 | NULL    | Yes      |      24 |
    | utf8mb4_0900_as_ci         | utf8mb4      |  305 | NULL    | Yes      |       0 |
    | utf8mb4_ru_0900_ai_ci      | utf8mb4      |  306 | NULL    | Yes      |       0 |
    | utf8mb4_ru_0900_as_cs      | utf8mb4      |  307 | NULL    | Yes      |       0 |
    | utf8mb4_zh_0900_as_cs      | utf8mb4      |  308 | NULL    | Yes      |       0 |
    | utf8mb4_0900_bin           | utf8mb4      |  309 | NULL    | Yes      |       1 |
    | utf8mb4_nb_0900_ai_ci      | utf8mb4      |  310 | NULL    | Yes      |       0 |
    | utf8mb4_nb_0900_as_cs      | utf8mb4      |  311 | NULL    | Yes      |       0 |
    | utf8mb4_nn_0900_ai_ci      | utf8mb4      |  312 | NULL    | Yes      |       0 |
    | utf8mb4_nn_0900_as_cs      | utf8mb4      |  313 | NULL    | Yes      |       0 |
    | utf8mb4_sr_latn_0900_ai_ci | utf8mb4      |  314 | NULL    | Yes      |       0 |
    | utf8mb4_sr_latn_0900_as_cs | utf8mb4      |  315 | NULL    | Yes      |       0 |
    | utf8mb4_bs_0900_ai_ci      | utf8mb4      |  316 | NULL    | Yes      |       0 |
    | utf8mb4_bs_0900_as_cs      | utf8mb4      |  317 | NULL    | Yes      |       0 |
    | utf8mb4_bg_0900_ai_ci      | utf8mb4      |  318 | NULL    | Yes      |       0 |
    | utf8mb4_bg_0900_as_cs      | utf8mb4      |  319 | NULL    | Yes      |       0 |
    | utf8mb4_gl_0900_ai_ci      | utf8mb4      |  320 | NULL    | Yes      |       0 |
    | utf8mb4_gl_0900_as_cs      | utf8mb4      |  321 | NULL    | Yes      |       0 |
    | utf8mb4_mn_cyrl_0900_ai_ci | utf8mb4      |  322 | NULL    | Yes      |       0 |
    | utf8mb4_mn_cyrl_0900_as_cs | utf8mb4      |  323 | NULL    | Yes      |       0 |
    +----------------------------+--------------+------+---------+----------+---------+
    149 rows in set (0.003 sec)
   

   需要注意某个具体的字符集跟它支持的字符序是强相关的：

    obclient> show collation where charset like 'gbk';
    +----------------+---------+------+---------+----------+---------+
    | COLLATION      | CHARSET | ID   | DEFAULT | COMPILED | SORTLEN |
    +----------------+---------+------+---------+----------+---------+
    | gbk_chinese_ci | gbk     |   28 | Yes     | Yes      |       1 |
    | gbk_bin        | gbk     |   87 | NULL    | Yes      |       1 |
    +----------------+---------+------+---------+----------+---------+
    2 rows in set (0.004 sec)
   
    obclient> show collation where charset like 'latin1';
    +-------------------+---------+------+---------+----------+---------+
    | COLLATION         | CHARSET | ID   | DEFAULT | COMPILED | SORTLEN |
    +-------------------+---------+------+---------+----------+---------+
    | latin1_swedish_ci | latin1  |    8 | Yes     | Yes      |       1 |
    | latin1_german1_ci | latin1  |    5 | NULL    | Yes      |       1 |
    | latin1_danish_ci  | latin1  |   15 | NULL    | Yes      |       1 |
    | latin1_german2_ci | latin1  |   31 | NULL    | Yes      |       1 |
    | latin1_general_ci | latin1  |   48 | NULL    | Yes      |       1 |
    | latin1_general_cs | latin1  |   49 | NULL    | Yes      |       1 |
    | latin1_spanish_ci | latin1  |   94 | NULL    | Yes      |       1 |
    | latin1_bin        | latin1  |   47 | NULL    | Yes      |       1 |
    +-------------------+---------+------+---------+----------+---------+
    8 rows in set (0.005 sec)
   

2. 创建业务租户时支持指定该租户的字符集（可选）和字符序（可选）。

   示例如下：

    CREATE TENANT IF NOT EXISTS test_tenant PRIMARY_ZONE='zone1;zone2,zone3', RESOURCE_POOL_LIST=('pool1'), CHARSET='utf8mb4' SET ob_tcp_invited_nodes TO '%';
   
    create tenant tenant_mysql replica_num = 1, resource_pool_list = ('mysql_pool'), charset = ut8mb4, collate = utf8mb4_bin set ob_tcp_invited_nodes='%', ob_compatibility_mode='mysql';
   
    CREATE TENANT tenant_charset_test REPLICA_NUM = 1, RESOURCE_POOL_LIST = ('pool_charset_test'), charset = 'gbk', set ob_tcp_invited_nodes='%', ob_compatibility_mode='oracle';
   

   注意

   从 OceanBase 数据库 V2.2.x 版本开始，创建 Oracle 租户时仅支持设置租户的字符集，不支持设置字符序，系统会使用所配置字符集的默认字符序。

3. 查看当前业务租户的字符集和字符序。

   • MySQL 模式租户。

     MySQL [(none)]> show variables like '%character%';
     +--------------------------+---------+
     | Variable_name            | Value   |
     +--------------------------+---------+
     | character_set_client     | utf8mb4 |
     | character_set_connection | utf8mb4 |   <== 常量使用的字符集
     | character_set_database   | utf8mb4 |   <== 对应租户的字符集
     | character_set_filesystem | binary  |
     | character_set_results    | utf8mb4 |   <== The character set which server should translate to before shipping result sets or error message back to the client
     | character_set_server     | utf8mb4 |
     | character_set_system     | utf8mb4 |
     +--------------------------+---------+
     7 rows in set (0.00 sec)
     
     MySQL [(none)]> show global variables like '%coll%';
     +-------------------------------+--------------------+
     | Variable_name                 | Value              |
     +-------------------------------+--------------------+
     | collation_connection          | utf8mb4_general_ci |   <== 常量使用的字符序
     | collation_database            | utf8mb4_general_ci |   <== 创建 database 时候默认的字符序
     | collation_server              | utf8mb4_general_ci |   <== 修改 collation_server 不会影响已经创建的表
     | default_collation_for_utf8mb4 | utf8mb4_general_ci |
     +-------------------------------+--------------------+
     4 rows in set (0.00 sec)
     

   • Oracle 模式租户。

     obclient [SYS]> SELECT value FROM v$nls_parameters WHERE parameter='NLS_CHARACTERSET';
     +----------+
     | VALUE    |
     +----------+
     | AL32UTF8 |
     +----------+
     1 row in set (0.001 sec)
     
     obclient [SYS]> show global variables like '%nls%';
     +-------------------------+------------------------------+
     | VARIABLE_NAME           | VALUE                        |
     +-------------------------+------------------------------+
     | nls_calendar            | GREGORIAN                    |
     | nls_characterset        | AL32UTF8                     |   <== Oracle 租户的字符集
     | nls_comp                | BINARY                       |
     | nls_currency            | $                            |
     | nls_date_format         | DD-MON-RR                    |
     | nls_date_language       | AMERICAN                     |
     | nls_dual_currency       | $                            |
     | nls_iso_currency        | AMERICA                      |
     | nls_length_semantics    | BYTE                         |
     | nls_nchar_characterset  | AL16UTF16                    |
     | nls_nchar_conv_excp     | FALSE                        |
     | nls_numeric_characters  | .,                           |
     | nls_sort                | BINARY                       |
     | nls_territory           | AMERICA                      |
     | nls_timestamp_format    | DD-MON-RR HH.MI.SSXFF AM     |
     | nls_timestamp_tz_format | DD-MON-RR HH.MI.SSXFF AM TZR |
     +-------------------------+------------------------------+
     16 rows in set (0.002 sec)
     
     obclient [SYS]> show global variables like '%coll%';
     +----------------------+-------------+
     | VARIABLE_NAME        | VALUE       |
     +----------------------+-------------+
     | collation_connection | utf8mb4_bin |   <== 常量使用的字符序
     +----------------------+-------------+
     1 row in set (0.004 sec)
     
     obclient [SYS]> select unique collation from dba_ob_databases;
     +-------------+
     | COLLATION   |
     +-------------+
     | utf8mb4_bin |   <== Oracle 租户下所有 schema 的字符序均一致，不支持单独设置
     +-------------+
     1 row in set (0.001 sec)
     

4. 租户一旦创建好之后不支持修改租户级别的字符集和字符序。

    MySQL [oceanbase]> alter tenant mysqlt charset=gbk;
    ERROR 1235 (0A000): tenant change charset not supported
   
    MySQL [oceanbase]> alter tenant oraclet charset=gbk;
    ERROR 1235 (0A000): tenant change charset not supported
   
    MySQL [oceanbase]> alter tenant mysqlt collate=utf8mb4_bin;
    ERROR 1235 (0A000): tenant change collation not supported
   
    MySQL [oceanbase]> alter tenant oraclet collate=utf8mb4_bin;
    ERROR 1235 (0A000): tenant change collation not supported
   

5. Oracle 租户只支持设置租户级别的字符集。

   OceanBase 数据库 Oracle 租户不支持为 user、database、table 或 column 单独设置字符集或字符序，只支持设置租户级别的字符集，跟原生的 Oracle 保持一致（原生 Oracle 不支持指定表的字符集。在原生 Oracle 数据库中，字符集是在数据库级别设置的，而不是针对单个表进行设置的）。

   obclient [SYS]> create table t1 (name varchar(10) charset gbk);
   ORA-00600: internal error code, arguments: -4007, set charset in oracle mode not supported
   

6. MySQL 租户支持设置和修改 database/table/column 级别的字符集和字符序。

   注意

   OceanBase 数据库 V2.x/V3.x 中 MySQL 租户只支持修改 database 的字符集/字符序。

   • 创建时指定字符集、字符序。

     MySQL [(none)]> create database db1 character set 'utf8mb4';
     MySQL [(none)]> create database db2 character set 'gbk' collate 'gbk_bin';
     MySQL [test]> create table nocase (name varchar(100) primary key) charset=utf8mb4 collate=utf8mb4_bin;
     MySQL [test]> create table t1 (name varchar(10) charset gbk);
     MySQL [test]> create table t2 (name varchar(100)) collate=utf8mb4_unicode_ci;
     

   • 修改数据库的字符集、字符序。

     MySQL [(none)]> alter database test character set 'utf8mb4';
     MySQL [(none)]> alter database test character set 'utf8mb4' collate 'utf8mb4_bin';
     MySQL [(none)]> alter database lower_db character set 'utf8mb4' collate 'utf8mb4_bin';
     MySQL [(none)]> alter database test character set 'utf8mb4' collate 'utf8mb4_bin';
     MySQL [(none)]> ALTER DATABASE test2 DEFAULT CHARACTER SET utf8mb4;
     MySQL [(none)]> ALTER DATABASE test2 DEFAULT COLLATE utf8mb4_bin;
     

     注意

     变更数据库的 charset/collation 不会影响现有表的 charset/collation，只会影响后续新建表的 charset/collation，因为存量表、列的 charset/collations 都已经持久化到 oceanbase.__all_table、oceanbase.__all_column 中去了。变更表的 charset/collation 时情况类似。

   • OceanBase 数据库 V4.x 中支持修改某张表的字符集、字符序（OceanBase 数据库 V2.x/V3.x 中不支持）。

     MySQL [test]> alter table nocase collate=utf8mb4_bin;
     MySQL [test]> alter table t1 collate=utf8mb4_bin;
     

   • OceanBase 数据库 V4.x 中支持修改某列的字符集、字符序（OceanBase 数据库 V2.x/V3.x 中不支持）。

     MySQL [test]> alter table nocase modify name varchar(100) collate utf8mb4_bin;
     

7. MySQL 租户下 database/table/column 级别字符集和字符序的优先级。

   MySQL 租户下 database/table/column 级别字符集和字符序的优先级如下：租户级别的设置 < database 级别的设置 < table 级别的设置 < column 级别的设置 < create、alter 语句中指定的字符集和字符序。

   具体如下：

   • 对 database 而言，如果创建时指定了字符集/字符序，以实际指定的为准；如果未指定，则自动继承所属租户的字符集/字符序。

   • 对 table 而言，如果创建时指定了字符集/字符序，以实际指定的为准；如果未指定，则自动继承所属 database 的字符集/字符序。

   • 对 column 而言，如果创建时指定了字符集/字符序，以实际指定的为准；如果未指定，则自动继承所属 table 的字符集/字符序。

   • 变更数据库的 charset/collation 不会影响现有表的 charset/collation，只会影响后续新建表的 charset/collation，因为存量表、列的 charset/collations 都已经持久化到 oceanbase.__all_table、oceanbase.__all_column 中去了。变更表的 charset/collation 时情况类似。

8. 同一字符集下不同的字符序会影响字符串比较的结果。

   以点号（.）和下划线（_）为例，如果采用 utf8mb4 字符集默认的字符序 utf8mb4_general_ci，则点号（.）< 下划线（_）；如果采用 utf8mb4_unicode_ci 进行比较，则点号（.）> 下划线（_）：

    -- 在 OceanBase 数据库 MySQL 中
    MySQL [test]> SELECT
    '.' AS char1,
    '_' AS char2,
    CASE
        WHEN '.' < '_' COLLATE "utf8mb4_general_ci" THEN 'char1 < char2'
        WHEN '.' > '_' COLLATE "utf8mb4_general_ci" THEN 'char1 > char2'
        ELSE 'char1 = char2'
    END AS comparison_result_en_us;
    +-------+-------+-------------------------+
    | char1 | char2 | comparison_result_en_us |
    +-------+-------+-------------------------+
    | .     | _     | char1 < char2           |
    +-------+-------+-------------------------+
    1 row in set (0.001 sec)
   
    MySQL [test]> SELECT
    '.' AS char1,
    '_' AS char2,
    CASE
        WHEN '.' < '_' COLLATE "utf8mb4_unicode_ci" THEN 'char1 < char2'
        WHEN '.' > '_' COLLATE "utf8mb4_unicode_ci" THEN 'char1 > char2'
        ELSE 'char1 = char2'
    END AS comparison_result_en_us;
    +-------+-------+-------------------------+
    | char1 | char2 | comparison_result_en_us |
    +-------+-------+-------------------------+
    | .     | _     | char1 > char2           |
    +-------+-------+-------------------------+
    1 row in set (0.005 sec)
   
    MySQL [test]> select ascii('.'), ascii('_');
    +------------+------------+
    | ascii('.') | ascii('_') |
    +------------+------------+
    |         46 |         95 |
    +------------+------------+
    1 row in set (0.001 sec)
   

适用版本

OceanBase 数据库 V2.x/V3.x/V4.x 版本。