mysql 修改表、列的字符集和校对规则
来源:互联网 发布:delphi算法与数据结构 编辑:程序博客网 时间:2024/05/15 17:58
将表的所有数据转为另外一种字符集和校对规则
To change the table default character set and all character columns (CHAR
, VARCHAR
, TEXT
) to a new character set, use a statement like this:
ALTER TABLE tbl_name
CONVERT TO CHARACTER SET charset_name
[COLLATE collation_name
];
The statement also changes the collation of all character columns. If you specify no COLLATE
clause to indicate which collation to use, the statement uses default collation for the character set. If this collation is inappropriate for the intended table use (for example, if it would change from a case-sensitive collation to a case-insensitive collation), specify a collation explicitly.
For a column that has a data type of VARCHAR
or one of the TEXT
types, CONVERT TO CHARACTER SET
will change the data type as necessary to ensure that the new column is long enough to store as many characters as the original column. For example, a TEXT
column has two length bytes, which store the byte-length of values in the column, up to a maximum of 65,535. For a latin1
TEXT
column, each character requires a single byte, so the column can store up to 65,535 characters. If the column is converted to utf8
, each character might require up to three bytes, for a maximum possible length of 3 × 65,535 = 196,605 bytes. That length will not fit in a TEXT
column's length bytes, so MySQL will convert the data type to MEDIUMTEXT
, which is the smallest string type for which the length bytes can record a value of 196,605. Similarly, a VARCHAR
column might be converted to MEDIUMTEXT
.
注意:
使用convert to character set charset_name,如果转换后的数据类型不能存储全部数据,会发生数据类型变化。
比如text最多可以存储65535个字节,latin1字符集下,一个字符占用一个字节,所以也就是65535个字符,转换为utf8,一个字符至多可以占用3个字节,所以,最坏的情况,转换后就需要65535*3个字节,超出text容量,Mysql会自动将数据类型转为mediumtext
修改某列的字符集
To avoid data type changes of the type just described, do not use CONVERT TO CHARACTER SET
. Instead, use MODIFY
to change individual columns. For example:
ALTER TABLE t MODIFY latin1_text_col TEXT CHARACTER SET utf8;ALTER TABLE t MODIFY latin1_varchar_col VARCHAR(M
) CHARACTER SET utf8;
If you specify CONVERT TO CHARACTER SET binary
, the CHAR
, VARCHAR
, and TEXT
columns are converted to their corresponding binary string types (BINARY
, VARBINARY
, BLOB
). This means that the columns no longer will have a character set and a subsequent CONVERT TO
operation will not apply to them.
If charset_name
is DEFAULT
, the database character set is used.
修改某表的默认字符集和校对规则
只会对以后的列有影响,对已经创建的列,不会产生影响。
To change only the default character set for a table, use this statement:
参考ALTER TABLEtbl_name
DEFAULT CHARACTER SETcharset_name [COLLATE collation_name]
;
点击打开链接
- mysql 修改表、列的字符集和校对规则
- mysql字符集和校对规则
- MySQL字符集和校对规则
- MySQL字符集和校对规则
- MySQL字符集和校对规则
- MYSQL的字符集——字符集和校对规则
- mysql字符集和校对规则(Mysql校对集)
- mysql字符集和校对规则(Mysql校对集)
- mysql字符集和校对规则(Mysql校对集)
- mysql 查看字符集和校对规则 命令
- MySQL 中的字符集和校对规则
- 37.mysql 字符集和校对规则
- 字符集和校对规则
- 字符集和校对规则
- MySQL字符集的校对规则(collation rule)
- MySQL 字符集与校对规则
- Mysql字符集以及校对规则
- MySQL 字符集与校对规则
- apache ab并发测试工具
- android中的图片处理
- Nginx源码分析 - 基础数据结构篇 - hash表结构 ngx_hash.c
- FastCV主要接口分析之二
- Java-day07多线程
- mysql 修改表、列的字符集和校对规则
- WEB压测工具siege用法
- SIP中603/decline中的意义
- springMVC +mybatis的配置
- nginx配置备忘
- 好的开始,“first blood”
- MD5和SHA加密
- Linux epoll详解
- linux 配置开机自启动