如何设置数据库的LC_COLLATE, LC_CTYPE, ENCODING, TEMPLATE

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介:

标签

PostgreSQL , create database , collate , ctype , pg_encoding , pg_encoding_to_char()


背景

PostgreSQL实例支持创建多个数据库,创建数据库时,可以指定模板库,并为每个数据库设置不同的字符集、本地化collate, 货币等属性。

接下来举例讲述CREATE DATABASE的具体使用方法。

一、CREATE DATABASE语法

Command:     CREATE DATABASE  
Description: create a new database  
Syntax:  
CREATE DATABASE name  
    [ [ WITH ] [ OWNER [=] user_name ]  
           [ TEMPLATE [=] template ]  
           [ ENCODING [=] encoding ]  
           [ LC_COLLATE [=] lc_collate ]  
           [ LC_CTYPE [=] lc_ctype ]  
           [ TABLESPACE [=] tablespace_name ]  
           [ CONNECTION LIMIT [=] connlimit ] ]  

二、克隆模板库(从指定模板创建数据库)

例子

以test数据库为模板,克隆一个名为test01的数据库。

test=> create database test01 with template test;  
CREATE DATABASE  

如果不指定模板,则默认的模板为template1。

注意

从指定模板库创建数据库,除了当前连接,不能有其他用户连在对应的模板库上面。

如果有其他用户连在test库,可能会报类似这样的错误

test=> create database test01 with template test;  
ERROR:  source database "test" is being accessed by other users  
DETAIL:  There is 1 other session using the database.  

三、创建数据库时,如何指定encoding

PostgreSQL支持哪些encoding

用户可以参考PostgreSQL的官方文档,有对应的字符集支持列表

https://www.postgresql.org/docs/9.6/static/multibyte.html

Server=Yes表示该字符集支持用于create database。否则只支持作为客户端字符集。

Name Description Language Server? Bytes/Char Aliases
BIG5 Big Five Traditional Chinese No 1-2 WIN950, Windows950
EUC_CN Extended UNIX Code-CN Simplified Chinese Yes 1-3 -
EUC_JP Extended UNIX Code-JP Japanese Yes 1-3 -
EUC_JIS_2004 Extended UNIX Code-JP, JIS X 0213 Japanese Yes 1-3 -
EUC_KR Extended UNIX Code-KR Korean Yes 1-3 -
EUC_TW Extended UNIX Code-TW Traditional Chinese, Taiwanese Yes 1-3 -
GB18030 National Standard Chinese No 1-4 -
GBK Extended National Standard Simplified Chinese No 1-2 WIN936, Windows936
ISO_8859_5 ISO 8859-5, ECMA 113 Latin/Cyrillic Yes 1 -
ISO_8859_6 ISO 8859-6, ECMA 114 Latin/Arabic Yes 1 -
ISO_8859_7 ISO 8859-7, ECMA 118 Latin/Greek Yes 1 -
ISO_8859_8 ISO 8859-8, ECMA 121 Latin/Hebrew Yes 1 -
JOHAB JOHAB Korean (Hangul) No 1-3 -
KOI8R KOI8-R Cyrillic (Russian) Yes 1 KOI8
KOI8U KOI8-U Cyrillic (Ukrainian) Yes 1 -
LATIN1 ISO 8859-1, ECMA 94 Western European Yes 1 ISO88591
LATIN2 ISO 8859-2, ECMA 94 Central European Yes 1 ISO88592
LATIN3 ISO 8859-3, ECMA 94 South European Yes 1 ISO88593
LATIN4 ISO 8859-4, ECMA 94 North European Yes 1 ISO88594
LATIN5 ISO 8859-9, ECMA 128 Turkish Yes 1 ISO88599
LATIN6 ISO 8859-10, ECMA 144 Nordic Yes 1 ISO885910
LATIN7 ISO 8859-13 Baltic Yes 1 ISO885913
LATIN8 ISO 8859-14 Celtic Yes 1 ISO885914
LATIN9 ISO 8859-15 LATIN1 with Euro and accents Yes 1 ISO885915
LATIN10 ISO 8859-16, ASRO SR 14111 Romanian Yes 1 ISO885916
MULE_INTERNAL Mule internal code Multilingual Emacs Yes 1-4 -
SJIS Shift JIS Japanese No 1-2 Mskanji, ShiftJIS, WIN932, Windows932
SHIFT_JIS_2004 Shift JIS, JIS X 0213 Japanese No 1-2 -
SQL_ASCII unspecified (see text) any Yes 1 -
UHC Unified Hangul Code Korean No 1-2 WIN949, Windows949
UTF8 Unicode, 8-bit all Yes 1-4 Unicode
WIN866 Windows CP866 Cyrillic Yes 1 ALT
WIN874 Windows CP874 Thai Yes 1 -
WIN1250 Windows CP1250 Central European Yes 1 -
WIN1251 Windows CP1251 Cyrillic Yes 1 WIN
WIN1252 Windows CP1252 Western European Yes 1 -
WIN1253 Windows CP1253 Greek Yes 1 -
WIN1254 Windows CP1254 Turkish Yes 1 -
WIN1255 Windows CP1255 Hebrew Yes 1 -
WIN1256 Windows CP1256 Arabic Yes 1 -
WIN1257 Windows CP1257 Baltic Yes 1 -
WIN1258 Windows CP1258 Vietnamese Yes 1 ABC, TCVN, TCVN5712, VSCII

创建指定encoding的数据库

例子

创建一个UTF-8字符集的数据库

test=> create database test02 with encoding 'UTF-8';  
CREATE DATABASE  

注意

1. 指定的字符集必须是模板库字符集的超集,否则会报错。

2. 指定的lc_ctype和lc_collate必须与目标字符集兼容。

例子,template1是默认模板库,它的字符集为UTF8。

test=> \l template1  
                                  List of databases  
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges     
-----------+----------+----------+-------------+-------------+-----------------------  
 template1 | xxxxxxxx | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/xxxxxxxx          +  
           |          |          |             |             | xxxxxxxx=CTc/xxxxxxxx  
(1 row)  

创建一个EUC_CN字符集的数据库

报错1,EUC_CN字符集与模板库的lc_collate,lc_ctype不兼容。

test=> create database test03 with encoding 'EUC_CN';  
ERROR:  encoding "EUC_CN" does not match locale "zh_CN.UTF-8"  
DETAIL:  The chosen LC_CTYPE setting requires encoding "UTF8".  

报错2,EUC_CN字符集与模板库的字符集UTF-8不兼容。

test=> create database test03 with encoding 'EUC_CN' lc_collate='C' lc_ctype='C';  
ERROR:  new encoding (EUC_CN) is incompatible with the encoding of the template database (UTF8)  
HINT:  Use the same encoding as in the template database, or use template0 as template.  

四、如何获取字符集支持的LC_COLLATE, LC_CTYPE信息

使用如下SQL可以查询系统表pg_collation得到字符集支持的lc_collate和lc_ctype。

其中encoding为空时,表示这个collation支持所有的字符集。

test=> select pg_encoding_to_char(collencoding) as encoding,collname,collcollate,collctype from pg_collation ;  
  encoding  |       collname        |      collcollate      |       collctype         
------------+-----------------------+-----------------------+-----------------------  
            | default               |                       |   
            | C                     | C                     | C  
            | POSIX                 | POSIX                 | POSIX  
 UTF8       | aa_DJ                 | aa_DJ.utf8            | aa_DJ.utf8  
 LATIN1     | aa_DJ                 | aa_DJ                 | aa_DJ  
 LATIN1     | aa_DJ.iso88591        | aa_DJ.iso88591        | aa_DJ.iso88591  
 UTF8       | aa_DJ.utf8            | aa_DJ.utf8            | aa_DJ.utf8  
 UTF8       | aa_ER                 | aa_ER                 | aa_ER  
 UTF8       | aa_ER.utf8            | aa_ER.utf8            | aa_ER.utf8  
.......  
 EUC_CN     | zh_CN                 | zh_CN                 | zh_CN  
 UTF8       | zh_CN                 | zh_CN.utf8            | zh_CN.utf8  
 EUC_CN     | zh_CN.gb2312          | zh_CN.gb2312          | zh_CN.gb2312  
 UTF8       | zh_CN.utf8            | zh_CN.utf8            | zh_CN.utf8  
 UTF8       | zh_HK                 | zh_HK.utf8            | zh_HK.utf8  
 UTF8       | zh_HK.utf8            | zh_HK.utf8            | zh_HK.utf8  
 EUC_CN     | zh_SG                 | zh_SG                 | zh_SG  
 UTF8       | zh_SG                 | zh_SG.utf8            | zh_SG.utf8  
 EUC_CN     | zh_SG.gb2312          | zh_SG.gb2312          | zh_SG.gb2312  
 UTF8       | zh_SG.utf8            | zh_SG.utf8            | zh_SG.utf8  
 EUC_TW     | zh_TW                 | zh_TW.euctw           | zh_TW.euctw  
 UTF8       | zh_TW                 | zh_TW.utf8            | zh_TW.utf8  
 EUC_TW     | zh_TW.euctw           | zh_TW.euctw           | zh_TW.euctw  
 UTF8       | zh_TW.utf8            | zh_TW.utf8            | zh_TW.utf8  
 UTF8       | zu_ZA                 | zu_ZA.utf8            | zu_ZA.utf8  
 LATIN1     | zu_ZA                 | zu_ZA                 | zu_ZA  
 LATIN1     | zu_ZA.iso88591        | zu_ZA.iso88591        | zu_ZA.iso88591  
 UTF8       | zu_ZA.utf8            | zu_ZA.utf8            | zu_ZA.utf8  
(869 rows)  

五、创建数据库时指定LC_COLLATE, LC_CTYPE

例子

创建一个数据库,lc_collate, lc_ctype分别为zh_CN.utf8

test=> create database test05 with encoding 'UTF-8' template template0 lc_collate='zh_CN.utf8' lc_ctype='zh_CN.utf8';  
CREATE DATABASE  

注意

如果指定的lc_collate, lc_ctype与模板库的collate,ctype不兼容,会报错。

test=> create database test04 with encoding 'UTF-8' lc_collate='zh_CN.utf8' lc_ctype='zh_CN.utf8';  
ERROR:  new collation (zh_CN.utf8) is incompatible with the collation of the template database (zh_CN.UTF-8)  
HINT:  Use the same collation as in the template database, or use template0 as template.  

解决办法1,使用兼容的collate和ctype。

test=> create database test04 with encoding 'UTF-8' lc_collate='zh_CN.UTF-8' lc_ctype='zh_CN.UTF-8';  
CREATE DATABASE  

解决办法2,使用template0作为模板库。

test=> create database test05 with encoding 'UTF-8' template template0 lc_collate='zh_CN.utf8' lc_ctype='zh_CN.utf8';  
CREATE DATABASE  

六、如何修改已有数据库的collate,ctype

目前无法直接通过alter database的语法进行修改,用户可以使用创建新的数据库,导出,再导入的方式。

例子

1. 创建新数据库,指定目标collate和ctype

2. 使用pg_dump或其他客户端工具逻辑导出源数据库的数据

3. 使用pg_restore或其他客户端工具,将第二步导出数据导入新数据库

参考

https://www.postgresql.org/docs/9.6/static/sql-createdatabase.html

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
1月前
|
关系型数据库 MySQL Linux
Linux系统如何设置自启动服务在MySQL数据库启动后执行?
【10月更文挑战第25天】Linux系统如何设置自启动服务在MySQL数据库启动后执行?
74 3
|
1月前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时,文章还对比了编译源码安装与使用 RPM 包安装的优缺点,帮助读者根据需求选择最合适的方法。通过具体案例,展示了编译源码安装的灵活性和定制性。
86 2
|
2月前
|
存储 分布式计算 数据库
阿里云国际版设置数据库云分析工作负载的 ClickHouse 版
阿里云国际版设置数据库云分析工作负载的 ClickHouse 版
|
2月前
|
应用服务中间件 数据库
Tomcat 的数据库连接池设置与应用
Tomcat 的数据库连接池设置与应用
44 3
|
2月前
|
关系型数据库 MySQL 数据库
使用Docker部署的MySQL数据库如何设置忽略表名大小写?
【10月更文挑战第1天】使用Docker部署的MySQL数据库如何设置忽略表名大小写?
192 1
|
3月前
|
SQL 关系型数据库 MySQL
MySQL数据库中给表添加字段并设置备注的脚本编写
通过上述步骤,你可以在MySQL数据库中给表成功添加新字段并为其设置备注。这样的操作对于保持数据库结构的清晰和最新非常重要,同时也帮助团队成员理解数据模型的变化和字段的具体含义。在实际操作中,记得调整脚本以适应具体的数据库和表名称,以及字段的详细规范。
66 8
|
4月前
|
存储 关系型数据库 MySQL
"Linux环境下MySQL数据库名及表名大小写敏感性设置详解:从配置到影响,确保数据库操作的准确与高效"
【8月更文挑战第9天】在Linux环境中,MySQL数据库名及表名的大小写敏感性是一项重要配置。默认情况下,MySQL在Linux上区分大小写,但这可通过配置文件 `/etc/my.cnf` 中的 `lower_case_table_names` 参数调整。该参数设为0时,名称存储时保持原样,查询时不区分大小写;设为1则全部转换为小写。通过编辑配置文件并重启MySQL服务,可根据需求灵活控制名称的大小写敏感性,确保数据一致性和应用兼容性。
267 3
|
5月前
|
SQL Oracle 关系型数据库
关系型数据库Oracle设置 RMAN 环境:
【7月更文挑战第25天】
72 2
|
5月前
|
关系型数据库 分布式数据库 数据库
PolarDB产品使用问题之在部署PolarDB-Kubernetes时,如何设置数据库密码
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
4月前
|
关系型数据库 MySQL 数据库
设置MySQL 创建数据库,默认为UTF-8
设置MySQL 创建数据库,默认为UTF-8
25 0