Character set specifications

This topic describes the character set specifications in OceanBase Database.

You can set the character set at the tenant level, database level, table level, column level, or session level. OceanBase Database supports the utf8mb4, gbk, gb18030, binary, and utf16 character sets.

Note

• To ensure seamless data migration, OceanBase Database considers UTF8 and UTF8MB4 to be synonyms in syntax.
• The character set of a database cannot be modified.

Here is an example where the gbk character set is used:

• Set the character set when creating a tenant

  • You can add the "charset=gbk" parameter to the create tenant statement. Here is an example:

    create tenant oracle replica_num = 1,
    resource_pool_list =('pool1'),
    charset = gbk
    set
      ob_tcp_invited_nodes = '%',
      ob_compatibility_mode = 'oracle',
      parallel_servers_target = 10,
      ob_sql_work_area_percentage = 20,
      secure_file_priv = "";
    

  • You can select the gbk character set when you create a tenant in the OCP console.

    Notice

    • In Oracle mode, the character set is set at the tenant level. In a gbk tenant, all char, varchar2, and clob columns in user tables use the gbk character set, and char and varchar2 columns in system tables retain the utf8 character set.
    • The tenant character set in Oracle mode is immutable. You cannot use the alter statement to change the character sets of tenants, databases, tables, or columns.

• Set the client (link) character set

  • The client (link) character set is the character set configured for interactions between the client and server.

    The client sends SQL strings to the server for execution and receives the execution results from the server. The server needs to know the client's character set to correctly parse, execute, and return results. The client can be obclient, JDBC, or OCI in different environments. Therefore, the client character set is also referred to as the link character set.

  • The client character set is independent of the tenant character set. They are configured separately.

    A tenant using the gbk character set can be connected to by a client using the gbk character set or a client using the UTF8 character set. Here is an example:

    • If the client character set is gbk, the server parses and executes the received SQL statement using the gbk character set;

    • If the client character set is UTF8, the server parses and executes the received SQL statement using the UTF8 character set.

  • The methods of setting the client character set are as follows:

    • Permanent modification

      set global character_set_client = gbk;
      set global character_set_connection = gbk;
      set global character_set_results = gbk;
      

      • character_set_client: the client character set.

      • character_set_connection: the connection character set. In Oracle mode, it is recommended to set this parameter to the same value as character_set_client.

      • character_set_results: the character set of the results returned from the server to the client.

        Generally, the character sets for strings sent from the client to the server and from the server to the client are the same. Therefore, in Oracle mode, it is recommended to set the three parameters to the same value; in MySQL mode, the three parameters can be flexibly configured. Generally, all three parameters are configured to the value of the client character set in general scenarios.

    • Temporary modification (valid only for the current session):

      • Method 1:

        set character_set_client = gbk;
        set character_set_connection = gbk;
        set character_set_results = gbk;
        

      • Method 2:

        set names gbk;
        

• Set the client character set

  • When you use JDBC to connect to OceanBase Database, you can add the "characterEncoding=gbk" parameter to the URL to modify the parameter. Here is an example:

    String url = "jdbc:oceanbase://xxx.xxx.xxx.xxx:xxxx?useSSL=false&useUnicode=true&characterEncoding=gbk&connectTimeout=30000&rewriteBatchedStatements=true";
    

  • When you use obclient to connect to the database, we recommend that you use the superset zh_CN.GB18030 of the GBK character set for the bash environment variable.

    • Modify the bash environment variable:

      export LANG=zh_CN.GB18030
      export LC_ALL=zh_CN.GB18030
      

    • Modify the encoding setting of the terminal and set the current window to the gbk encoding. Perform operations based on the instructions in the terminal interface.

Notice

Do not forget to configure the client, driver, and operating system environment to the same character set as that of the database (observer). Otherwise, garbled characters may be displayed.