Mysql中varchar存放中文与英文所占字节异同

本文详细介绍了UTF-8、GBK编码的区别,UTF8MB4作为UTF-8的扩展,支持更多字符。在MySQL中,UTF-8每个汉字占用3字节,GBK每个汉字占用2字节,而varchar最大长度受制于MySQL的行大小限制。此外,还探讨了数值类型存储所占字节数及不同编码下varchar的最大存储数量。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

一、关于UTF-8

UTF-8 Unicode Transformation Format-8bit。是用以解决国际上字符的一种多字节编码。

它对英文使用8位(即一个字节) ,中文使用24位(三个字节)来编码。

UTF-8包含全世界所有国家需要用到的字符,是国际编码,通用性强。

UTF-8编码的文字可以在各国支持UTF8字符集额的浏览器上显示。
如果是UTF8编码,则在外国人的英文IE也能显示中文,他们无需下载IE的中文语言支持包。

二、关于GBK

GBK 是国家标准GB2312基础上扩容后兼容GB2312的标准。

GBK的文字编码是用双字节来表示的,即不论中、英文字符均使用双字节来表示,为了区分中文,将其最高位都设定成1。

GBK包含全部中文字符,是国家编码,通用性比UTF8差,不过UTF8占用的数据库比GBK大。

三、关于utf8mb4

MySql 5.5 之前,UTF8 编码只支持1-3个字节,只支持BMP这部分的unicode编码区,BMP是从哪到哪?
戳这里 基本就是 0000 ~ FFFF 这一区。

从MySQL 5.5 开始,可支持4个字节UTF编码utf8mb4,一个字符最多能有4字节,所以能支持更多的字符集。


utf8mb4 is a superset of utf8

tf8mb4兼容utf8,且比utf8能表示更多的字符。

至于什么时候用,看你做的什么项目了。。。
在做移动应用时,会遇到IOS用户在文本的区域输入emoji表情,如果不做一定处理,就会导致插入数据库异常。

四、汉字长度与编码有关

MySql 5.0 以上的版本:

1、一个汉字占多少长度与编码有关:

  • UTF-8:一个汉字 = 3个字节,英文是一个字节
  • GBK: 一个汉字 = 2个字节,英文是一个字节

2、varchar(n) 表示n个字符,无论汉字和英文,MySql都能存入 n 个字符,仅实际字节长度有所区别。

3、MySQL检查长度,可用SQL语言


SELECT LENGTH(fieldname) FROM tablename 

五、实际测试

1、首先使用utf8 创建 str_test 表。

    CREATE TABLE `str_test` (
        `name_chn` varchar(20) NOT NULL,
        `name_en`  varchar(20) NOT NULL
    ) ENGINE=InnoDB AUTO_INCREMENT=62974 DEFAULT CHARSET=utf8

然后插入值

    mysql> insert into  str_test values ('我爱Ruby', 'I Love Ruby!');
    Query OK, 1 row affected (0.02 sec)

打开irb

    >> "我爱Ruby".size
    => 6
    >> "I Love Ruby!".size
    => 12
    >>

从MySQL中查询出来的结果,对比

    mysql> select * from str_test;
    +------------+--------------+
    | name_chn   | name_en      |
    +------------+--------------+
    | 我爱Ruby   | I Love Ruby! |
    +------------+--------------+
    1 row in set (0.02 sec)


    mysql> select length(name_chn) from str_test;
    +------------------+
    | length(name_chn) |
    +------------------+
    |               10 |
    +------------------+
    1 row in set (0.01 sec)

3[一个汉字三字节] * 2 + 1[一个英文一字节] * 4 = 10

    mysql> select length(name_en) from str_test;
    +-----------------+
    | length(name_en) |
    +-----------------+
    |              12 |
    +-----------------+
    1 row in set (0.00 sec)

10[一个英文一字节] * 1 + 2[空格一字节] * whitespace = 12

2、使用 GBK 做测试

创建表

        CREATE TABLE `str_test` (
        `name_chn` varchar(20) NOT NULL,
        `name_en`  varchar(20) NOT NULL
    ) ENGINE=InnoDB AUTO_INCREMENT=62974 DEFAULT CHARSET=gbk

插入数据,并且测试

    mysql> insert into  str_test values ('我爱Ruby', 'I Love Ruby!');
    Query OK, 1 row affected (0.00 sec)

    mysql> select * from str_test;
    +------------+--------------+
    | name_chn   | name_en      |
    +------------+--------------+
    | 我爱Ruby   | I Love Ruby! |
    +------------+--------------+
    1 row in set (0.01 sec)

GBK 中文是两个字节,英文是一个字节。

    mysql> select length(name_chn) from str_test;
    +------------------+
    | length(name_chn) |
    +------------------+
    |                8 |
    +------------------+
    1 row in set (0.00 sec)

2[中文两个字节] * 2 + 4[英文一个字节] * 1 = 8

    mysql> select length(name_en) from str_test;
    +-----------------+
    | length(name_en) |
    +-----------------+
    |              12 |
    +-----------------+
    1 row in set (0.00 sec)

10[英文一个字节] * 1 + 2[空格一个字节] * whitespace = 12

六、关于varchar 最多能存多少值

  • mysql的记录行长度是有限制的,不是无限长的,这个长度是64K,即65535个字节,对所有的表都是一样的。

  • MySQL对于变长类型的字段会有1-2个字节来保存字符长度。

  • 当字符数小于等于255时,MySQL只用1个字节来记录,因为2的8次方减1只能存到255。

  • 当字符数多余255时,就得用2个字节来存长度了。

  • utf-8状态下的varchar,最大只能到 (65535 - 2) / 3 = 21844 余 1。

  • gbk状态下的varchar, 最大只能到 (65535 - 2) / 2 = 32766 余 1

使用 utf-8 创建

        mysql>     CREATE TABLE `str_test` (
        ->         `id`  tinyint(1)  NOT NULL,
        ->         `name_chn` varchar(21845) NOT NULL
        ->     ) ENGINE=InnoDB AUTO_INCREMENT=62974 DEFAULT CHARSET=utf8
        -> ;
        ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
        mysql>     CREATE TABLE `str_test` (
        ->         `id`  tinyint(1)  NOT NULL,
        ->         `name_chn` varchar(21844) NOT NULL
        ->     ) ENGINE=InnoDB AUTO_INCREMENT=62974 DEFAULT CHARSET=utf8
        ->
        ->
        -> ;
        Query OK, 0 rows affected (0.06 sec)

使用gbk创建

当存储长度为 32768 失败~

        mysql>     CREATE TABLE `str_test` (
        ->         `id`  tinyint(1)  NOT NULL,
        ->         `name_chn` varchar(32768) NOT NULL
        ->     ) ENGINE=InnoDB AUTO_INCREMENT=62974 DEFAULT CHARSET=gbk
        -> ;
    ERROR 1074 (42000): Column length too big for column 'name_chn' (max = 32767); use BLOB or TEXT instead

当存储长度为 32767 失败~

    mysql>     CREATE TABLE `str_test` (                                                                                                 ->         `id`  tinyint(1)  NOT NULL,
        ->         `name_chn` varchar(32767) NOT NULL
        ->     ) ENGINE=InnoDB AUTO_INCREMENT=62974 DEFAULT CHARSET=gbk
        -> ;
    ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

当存储长度为 32766 成功~

    mysql>     CREATE TABLE `str_test` (
        ->         `id`  tinyint(1)  NOT NULL,
        ->         `name_chn` varchar(32766) NOT NULL
        ->     ) ENGINE=InnoDB AUTO_INCREMENT=62974 DEFAULT CHARSET=gbk
        -> ;
    Query OK, 0 rows affected (0.03 sec)

smallint 用两个字节存储,所以

2[smallint] + 32766 * 2[varchar存储长度] + 2[2个字节来存长度] > 65535

所以失败~

     mysql>     CREATE TABLE `str_test` (
         ->         `id`  smallint(1)  NOT NULL,
         ->         `name_chn` varchar(32766) NOT NULL
         ->     ) ENGINE=InnoDB AUTO_INCREMENT=62974 DEFAULT CHARSET=gbk
         -> ;
     ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

#####七、数值类型所占的字节

类型所占字节
int4 字节
smallint2 字节
tinyint1 字节
decimal变长

官方关于decimal 的描述如下

Values for DECIMAL (and NUMERIC) columns are represented using a binary format that packs nine decimal (base 10) digits into four bytes.

Storage for the integer and fractional parts of each value are determined separately.

Each multiple of nine digits requires four bytes, and the “leftover” digits require some fraction of four bytes.

The storage required for excess digits is given by the following table.

翻译为中文

使用二进制格式将9个十进制(基于10)数压缩为4个字节来表示DECIMAL列值。

每个值的整数和分数部分的存储分别确定。

每个9位数的倍数需要4个字节,并且“剩余的”位需要4个字节的一部分。

下表给出了超出位数的存储需求:

Leftover DigitsNumber Of Bytes
00
11
21
32
42
53
63
74
84

那:decimal(10,2)占几个字节?

1、首先 10 指的是整数与小数部分的总长度, 2指的是小数部分的长度。那么整数部分就只有 10 - 2 = 8 位

2、因为整数与小数的存储市各自独立确定的,所以他们各自所占用空间的综合就是所占的总空间了。

3、对表可知,整数部分8位占了4个字节,小数部分2位占了1个字节,所以decimal(10,2)总共占了 4 + 1 = 5 个字节。

4、decimal(6,2) 整数部分(6 - 2 = 4) 位占2字节,小数部分2位占1字节,总共占3字节。

八、总结

varchar 字段是将实际内容单独存储在聚簇索引之外,内容开头用1到2个字节表示实际长度(长度超过255时需要2个字节),因此最大长度不能超过65535。

  • UTF-8:一个汉字 = 3个字节,英文是一个字节
  • GBK: 一个汉字 = 2个字节,英文是一个字节

utf-8状态下,汉字最多可以存 21844个字符串, 英文也为 21844个字符串。

gbk状态下,汉字最多可以存 32766个字符串,英文也为 32766个字符串。

<think>我们创建一个MySQL表,要求如下: - id: 正整数、自增(通常使用INT UNSIGNED AUTO_INCREMENT) - name: 唯一(UNIQUE)、非空(NOT NULL) - age: 非空(NOT NULL)、默认值("未知"),注意:由于age通常为数值,但这里要求默认值为字符串"未知",所以我们需要考虑数据类型。但根据常规,age应该是整数,所以这里可能矛盾。但根据问题描述,我们按照要求,可以将age设置为字符串类型(比如VARCHAR)来存储“未知”。但更合理的可能是,如果age是数值,那么默认值设置为字符串就不合适。所以这里有两种理解: 1. 题目要求默认值为字符串“未知”,那么age字段应该使用字符串类型(如VARCHAR) 2. 或者题目可能希望用数值,但用某个特殊值(如-1)代表未知?但题目明确说了默认值为“未知”,所以这里我们按照字符串类型处理。 但是,通常年龄是整数,所以这里可能是一个设计问题。不过,我们按照题目要求来。 另外,sex(性别)可空、默认值“未知”,同样使用字符串类型。 因此,我们设计表结构如下: 字段:id, name, age, sex 具体: id: INT UNSIGNED AUTO_INCREMENT PRIMARY KEY name: VARCHAR(某长度) NOT NULL UNIQUE age: VARCHAR(10) NOT NULL DEFAULT '未知' -- 这里用VARCHAR存储,并设置默认值 sex: VARCHAR(10) DEFAULT '未知' -- 可空,所以不加NOT NULL 但是,注意:题目要求sex可空,所以我们可以允许NULL,同时默认值设为'未知'。那么当插入数据不指定sex时,会填充默认值'未知',而不是NULL。但题目要求可空,所以也可以插入NULL。所以我们的设计满足:如果不指定sex,则默认为'未知';如果显式设置为NULL,则存储NULL。 然而,在MySQL中,如果字段定义为DEFAULT '未知'且没有NOT NULL约束,那么不指定该字段时,会使用默认值;如果指定为NULL,则存储NULL。 另一种考虑:题目要求默认值“未知”,同时可空,所以我们可以这样定义。 但是,如果题目希望age也是整数,那么我们就不能存储字符串。所以我们需要明确。根据题目,它要求默认值为“未知”,所以只能使用字符串类型。如果要求是整数,那么默认值就不能是字符串。因此,这里按照题目要求,使用字符串类型。 如果实际开发中,需要存储数字,那么我们可以用NULL来表示未知,但题目要求非空,所以不能为NULL。因此,我们只能使用字符串类型。 创建表的SQL语句: ```sql CREATE TABLE example_table ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL UNIQUE, age VARCHAR(10) NOT NULL DEFAULT '未知', sex VARCHAR(10) DEFAULT '未知' ); ``` 注意:name字段长度根据实际需要调整,这里假设为100。 但是,我们也可以考虑使用ENUM类型来存储sex,但题目没有要求,且默认值也是“未知”,所以用字符串更通用。 所以,最终SQL如上。 §§相关问题§§ 1. 如果要将age字段改为整数类型,同时满足默认值表示“未知”的需求,有哪些替代方案? 2. 在name字段上创建唯一索引在列定义时使用UNIQUE约束有何异同? 3. 为什么自增主键通常推荐使用INT或BIGINT类型,而不是其他类型? 4. 如果插入数据时不指定age列的值,MySQL会如何处理? 5. 在MySQL中,如何修改已存在表的列定义,例如将sex列的默认值改为'保密'?</think>### MySQL建表语句及解析 ```sql CREATE TABLE user_info ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID', name VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名', age VARCHAR(10) NOT NULL DEFAULT '未知' COMMENT '年龄', sex ENUM('男','女','未知') DEFAULT '未知' COMMENT '性别' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ``` #### 字段设计解析: 1. **id 字段** - `INT UNSIGNED`:确保正整数(范围0~42亿) - `AUTO_INCREMENT`:自动生成连续唯一ID - `PRIMARY KEY`:设为主键(隐含NOT NULL约束) 2. **name 字段** - `VARCHAR(50)`:可变长度字符串(节省存储空间) - `NOT NULL`:禁止空值 - `UNIQUE`:创建唯一索引(保证无重复值) 3. **age 字段** - `VARCHAR(10)`:用字符串存储"未知"(比NULL更语义化) - `NOT NULL`:强制必须存值 - `DEFAULT '未知'`:未显式赋值时自动填充默认值 4. **sex 字段** - `ENUM('男','女','未知')`:枚举类型约束输入范围 - `DEFAULT '未知'`:未赋值时使用默认值 - 允许NULL:未显式声明`NOT NULL`(符合可空要求) #### 关键设计选择: - **年龄存储方案** 使用`VARCHAR`而非`INT`: ```mermaid graph LR A[需求] --> B{数值计算?} B -->|否| C[存储'未知'字符串] B -->|是| D[用-1等特殊值] ``` 若需数值计算可改为: `age SMALLINT UNSIGNED DEFAULT 0 COMMENT '0表示未知'` - **ENUM vs VARCHAR** - ✅ ENUM优势:存储空间小(1字节),自动校验输入 -VARCHAR优势:灵活添加新选项(ENUM需修改表结构) - **索引优化** `UNIQUE`会自动创建唯一索引: ```sql SHOW INDEX FROM user_info; /* 输出结果包含: Key_name: name Non_unique: 0 */ ```
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值