MySQL数据类型之字符串

于 2025-02-10 13:31:09 发布
阅读量1.2k 收藏 19
点赞数 29
CC 4.0 BY-SA版权
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/2403_84646142/article/details/145545444

 对于一些文本信息类的数据，如姓名、家庭住址等，在MySQL中适合保存为字符串类型，下面是MySQL中常用的字符串类型：

字符串类型

类型名类型说明
CHAR固定长度的字符串
VARCHAR可变长度的字符串
BINARY固定长度的二进制字符串
VARBINARY可变长度的二进制字符串
BLOB普通二进制数据
TEXT普通文本数据
ENUM枚举类型
SET字符串对象，可以有零个或多个值

binary：二进制                     var：（variable）可变的，多变的          blob：团            enum：（enumerate）列举，枚举

一.CHAR和VAECHAR

CHAR和VARCHAR类型的字段用于存储字符串数据

CHAR类型的字段用于存储固定长度的字符串，固定长度范围是0~255之间的任意整数

VARCHAR类型的字段用于存储可变长度的字符串，可变长度范围是0~65535之间的任意整数

1.CHAR类型的定义方式：CHAR(M)

M指的是字符串的最大长度。CHAR类型的字段会根据M分配存储空间，无论有没有被存满，都会占用存满时的存储空间

2.VARCHAR类型的定义方式：VARCHAR(M)

M同样指的是字符串的最大长度。但不同的是VARCHAR类型的字段会根据实际保存的字符个数来决定实际占用的存储空间

例如，VARCHAR(255)表示最多可以保存255个字符，在UTF-8字符集下，当保存255个中文字符时，这些中文字符占用255x3=765字节，

此外，VARCHAR类型还会多占用1~3字节来存储一些额外的信息

需要注意的是：在向CHAR和VARCAHR类型的字段中插入字符串时，如果插入的字符串尾部存在空格，CHAR类型的字段会除去空格后进行存储，VARCHAR类型的字段会保留空格完整地存储字符串

二.BINARY和VARBINARY

类似于CHAR和VARCHAR，不同的是BINARY和VARBINARY存储的是二进制字符串

1.BINARY类型的定义方式：BINARY(M)

M是二进制数据的最大字节长度。BINARY(M)的类型长度是固定的，如果未指定M，表示只能存储1字节

例如，BINARY(8)表示最多能存储8字节，如果存储的字段值不足M字节，将在数据的后面填充0以补齐指定长度

2.VARBINARY类型的定义方式：VARBINARY(M)

M是指二进制数据的最大字节长度。VARBINARY类型必须指定M，否则会报错。此外，VARBINARY除了存储数据本身外，还会多占用1~2字节来存储一些额外的信息

三.TEXT系列

通常用于存储文章内容、评论等较长的字符串

TEXT系列的数据类型包括以下几种：

TEXT系列类型
数据类型类型说明存储范围
TINYTEXT短文本数据0~L+1字节，其中L<2^{8}
TEXT普通文本数据0~L+2字节，其中L<2^{16}
MEDIUMTEXT中等文本数据0~L+3字节，其中L<2^{24}
LONGTEXT超大文本数据0~L+4字节，其中L<2^{32}

上表中L表示给定字符串值的实际长度（以字节为单位）

四.BLOB系列

通常用于存储图片、PDF文档、音频和视频等二进制数据

BLOB类型具体如下表所示：

BLOB类型
数据类型类型描述存储范围
TINYBLOB短二进制数据0~L+1字节，其中L<2^{8}
BLOB普通二进制数据0~L+2字节，其中L<2^{16}
MEDIUMBLOB中等二进制数据0~L+3字节，其中L<2^{24}
LONGBLOB超大二进制数据0~L+4字节，其中L<2^{32}

需要注意的是，BLOB类型的数据是根据二进制编码进行比较和排序的，而TEXT类型是根据文本模式进行比较和排序的

五.ENUM类型

占用1~2字节的存储空间，当ENUM类型包含1~255个成员时，需要1字节的存储空间；当包含255~65525个成员时，需要2字节的存储空间

枚举列表中的每一个成员都有一个索引值。索引值从1开始，依次递增

创建ENUM类型的方式如下：

ENUM('值1','值2',值3'',...'值n')

上述定义方式中，('值1','值2',值3'',...'值n')成为索引列表，该列表中的每一项，称为成员

ENUM类型的数据只能从成员中选取单个值，不能一次选取多个值。枚举列表中最多可以包含65535个值，每个值都有一个顺序编号，实际保存在记录中的是顺序编号而不是列表中的值，因此不必担心过长的值占用空间

但是在使用SELECT、INSERT等语句进行操作是，仍然使用列表中的值而不是使用顺序编号

例：

1.创建my_enum表，设置字段类型为enum，字段名为gender，枚举列表中包含male和female两个成员： 

create table my_enum (gender enum ('male','female'));

2.添加两条测试数据，向数据表中添加值为male和值为female的两条记录

insert my_enum values ('male'),('female');

3.查询一条gender为female的记录：

select * from my_enum where gender = 'female';

4.显示结果：

+--------+
| gender |
+--------+
| female |
+--------+
1 row in set (0.00 sec)

5.添加枚举列表中不存在的值进行测试，从执行结果中可以看出当添加枚举列表中不存在的值时会报错：

mysql> insert my_enum values ('m');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1

六.SET类型

SET类型用于保存字符串对象，可以有零个或多个值，每个值都必须从创建表时指定的允许值列表中选择，其定义格式与ENUM类型类似

SET('值1','值2',值3'',...'值n')

上述定义方式中，SET类型占用1、2、3、4或8字节，这取决于集合成员的数量，SET类型的列表中最多可以有64个成员

SET类型和ENUM类型的主要区别在于：SET类型可以从列表中选取一个或多个值来保存，多个值之间用逗号分隔，而ENUM类型的数据只能从列表中选取一个值来保存

SET类型和ENUM类型的优势在于：规范了数据本身，限定只能添加规定的数据项，查询速度比CHAR、VARCHAR类型快，节省存储空间

在使用SET类型和ENUM类型时注意事项如下：

1.ENUM和SET类型列表中的值都可以使用中文，但必须设置支持中文的字符集

例如：create table my_enum (gender enum('男','女')) charset = bgk;

2.ENUM和SET类型在填写列表、插入值、查找值等操作时，都会自动忽略末尾的空格

需要注意的是：当添加重复的SET类型成员时，MySQL会自动删除重复的成员

七.多学一招：JSON数据类型

JSON是一种轻量级的数据交换格式，由JavaScript语言发展而来

在MySQL 5.7.7版本中开始提供JSON数据类型；在MySQL 8版本中，JSON类型提供了可以自动验证的JSON文档和优化的存储结构，使得MySQL中存储和读取JSON类型的数据更加方便和高效

MySQL中JSON类型值常见的格式有两种，分别为JSON数组和JSON对象，示例如下：

# JSON数组

['abc',10,null,true,false]

# JSON对象

{"K1":"value","K2":10}

从上述示例可知，JSON数组使用方括号表示，多个值之间使用逗号分隔；JSON对象使用花括号表示，保存的数据是一组键值对

JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，易于阅读和编写，同时也易于机器解析和生成。在 MySQL 中，从 5.7 版本开始引入了对 JSON 数据类型的支持，使得存储和操作 JSON 数据变得更加方便。以下是 JSON 在 MySQL 中的用法和一些重要的注意事项。

以下为KIMI关于json的详细说明：

1. JSON 的基本结构

JSON 数据可以是以下几种类型：

  • JSON 对象：由键值对组成，键（key）必须是字符串，值（value）可以是字符串、数字、布尔值、数组或对象。例如：

    JSON复制

    {
  "name": "Alice",
  "age": 25,
  "is_student": false,
  "hobbies": ["reading", "swimming"]
}

  • JSON 数组：由一系列值组成，值可以是任何 JSON 类型。例如：

    JSON复制

    ["apple", "banana", 123, true, {"key": "value"}]

2. 在 MySQL 中使用 JSON 数据类型

2.1 创建表

在 MySQL 中，可以使用 JSON 数据类型来定义字段。例如：

sql复制

CREATE TABLE my_json_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data JSON
);
2.2 插入 JSON 数据

插入 JSON 数据时，确保数据格式正确。例如：

sql复制

INSERT INTO my_json_table (data) VALUES
('{"name": "Alice", "age": 25, "hobbies": ["reading", "swimming"]}');

如果要插入多个 JSON 数据，可以将它们放在一个 JSON 数组中：

sql复制

INSERT INTO my_json_table (data) VALUES
('[{"name": "Alice", "age": 25}, {"name": "Bob", "age": 30}]');
2.3 查询 JSON 数据

MySQL 提供了多种函数来查询和操作 JSON 数据：

  • JSON_EXTRACT：提取 JSON 数据中的特定值。例如：

    sql复制

    SELECT JSON_EXTRACT(data, '$.name') AS name FROM my_json_table;

  • JSON_UNQUOTE：去除 JSON 值的引号。例如：

    sql复制

    SELECT JSON_UNQUOTE(JSON_EXTRACT(data, '$.name')) AS name FROM my_json_table;

  • JSON_CONTAINS：检查 JSON 数据是否包含某个值。例如：

    sql复制

    SELECT * FROM my_json_table WHERE JSON_CONTAINS(data, '"reading"', '$.hobbies');

  • JSON_SEARCH：在 JSON 数据中搜索字符串。例如：

    sql复制

    SELECT * FROM my_json_table WHERE JSON_SEARCH(data, 'one', 'reading') IS NOT NULL;
2.4 更新 JSON 数据

可以使用 JSON_SET、JSON_REPLACE 和 JSON_REMOVE 等函数来更新 JSON 数据：

  • JSON_SET：添加或更新 JSON 数据。例如：

    sql复制

    UPDATE my_json_table SET data = JSON_SET(data, '$.age', 26) WHERE id = 1;

  • JSON_REPLACE：替换 JSON 数据中的值。例如：

    sql复制

    UPDATE my_json_table SET data = JSON_REPLACE(data, '$.hobbies[0]', 'painting') WHERE id = 1;

  • JSON_REMOVE：删除 JSON 数据中的键或值。例如：

    sql复制

    UPDATE my_json_table SET data = JSON_REMOVE(data, '$.hobbies[1]') WHERE id = 1;

3. 注意事项

3.1 JSON 格式

  • JSON 中的键（key）和字符串值（value）必须用双引号（"）括起来。

  • 数字、布尔值（true/false）和 null 不需要用引号。

  • JSON 数据必须是有效的格式，否则会导致错误。

3.2 性能问题

  • JSON 数据类型虽然方便，但查询和操作 JSON 数据的性能可能不如传统的关系型数据结构。

  • 如果需要频繁查询 JSON 数据中的某个字段，建议将该字段提取到单独的列中。

3.3 索引

  • MySQL 5.7.8 及以上版本支持对 JSON 数据创建虚拟列（generated columns），并为这些虚拟列创建索引。例如：

    sql复制

    ALTER TABLE my_json_table ADD COLUMN name VARCHAR(255) AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.name'))) STORED;
CREATE INDEX idx_name ON my_json_table (name);
3.4 数据完整性

  • JSON 数据类型不会自动验证 JSON 数据的结构，因此需要在应用层或通过数据库约束来确保数据的正确性。

4. 示例

假设有一个表 users，存储用户的 JSON 数据：

sql复制

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_data JSON
);

INSERT INTO users (user_data) VALUES
('{"name": "Alice", "age": 25, "hobbies": ["reading", "swimming"]}');

查询用户的年龄：

sql复制

SELECT JSON_UNQUOTE(JSON_EXTRACT(user_data, '$.age')) AS age FROM users;

更新用户的年龄：

sql复制

UPDATE users SET user_data = JSON_SET(user_data, '$.age', 26) WHERE id = 1;

通过以上内容，你可以更好地理解和使用 JSON 数据类型在 MySQL 中的用法和注意事项。

在 JSON 中，JSON 对象和JSON 数组是两种基本的数据结构，它们的定义和使用方式如下：

1. JSON 对象

JSON 对象是由一系列键值对（key-value pairs）组成的无序集合，用大括号 {} 包裹。每个键（key）必须是字符串类型，用双引号 " 括起来，值（value）可以是字符串、数字、布尔值、数组、对象或 null。

定义示例

JSON复制

{
  "name": "Alice",
  "age": 25,
  "is_student": true,
  "hobbies": ["reading", "swimming"],
  "address": {
    "street": "123 Main St",
    "city": "New York",
    "zip": "10001"
  }
}

  • name、age、is_student 是简单的键值对。

  • hobbies 是一个数组，作为对象的一个属性值。

  • address 是一个嵌套的对象。

2. JSON 数组

JSON 数组是一个有序的值集合，用方括号 [] 包裹。数组中的值可以是任何 JSON 支持的类型，包括字符串、数字、布尔值、对象、数组或 null。

定义示例

JSON复制

[
  "apple",
  123,
  true,
  null,
  {"name": "Alice", "age": 25},
  ["reading", "swimming"]
]

  • 数组中的第一个值是字符串 "apple"。

  • 第二个值是数字 123。

  • 第三个值是布尔值 true。

  • 第四个值是 null。

  • 第五个值是一个对象。

  • 第六个值是一个数组。

3. JSON 对象中的数组

数组也可以作为 JSON 对象的属性值。例如：

JSON复制

{
  "name": "Alice",
  "age": 25,
  "hobbies": ["reading", "swimming"]
}

在这个例子中，hobbies 是一个数组，作为对象的一个属性。

4. 嵌套结构

JSON 支持嵌套结构，即对象中可以包含数组，数组中可以包含对象。例如：

JSON复制

{
  "name": "Alice",
  "age": 25,
  "cars": [
    {"make": "Porsche", "model": "911"},
    {"make": "BMW", "model": "M3"}
  ]
}

在这个例子中，cars 是一个数组，数组中的每个元素都是一个对象。

注意事项

  • JSON 对象的键必须是字符串类型，用双引号括起来。

  • JSON 数组中的值可以是任何 JSON 支持的类型，但必须用逗号 , 分隔。

  • JSON 数据必须是有效的格式，否则会导致解析错误。

通过以上定义和示例，你可以更好地理解和使用 JSON 对象和数组。

小雨的伞
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值