「MySQL」- String Types（学习笔记）

类型概述

在某些情况下，MySQL 可能会将字符串列更改为与 CREATE TABLE 或 ALTER TABLE 语句中给出的类型不同的类型。参见：Section 13.1.18.7, “Silent Column Specification Changes”

MySQL 以字符为单位解释字符列定义中的长度规范。这适用于 CHAR，VARCHAR 和 TEXT 类型。

许多字符串类型的列定义时，可以指定列的字符集或排序规则。这些属性适用于 CHAR, VARCHAR, TEXT, ENUM, SET：

  • CHARACTER SET 属性指定字符集，COLLATE 属性指定字符集的排序规则。CHARACTER SET 和 COLLATE 属性中的一个或两个缺失时分配字符集和归类的规则在文档中Section 10.1.3.5, “Column Character Set and Collation”有所描述。CHARSET 与 CHARACTER SET 是同意的。
  • 为字符串数据类型指定 CHARACTER SET binary 属性会导致列被创建为相应的二进制字符串数据类型：CHAR 变为 BINARY，VARCHAR 变为 VARBINARY，TEXT 变为 BLOB。对于 ENUM 和 SET 数据类型，不会发生这种情况；它们被创建为已声明。假设您使用此定义指定表：
  • BINARY 属性是指定表默认字符集和该字符集的二进制（_bin）排序规则的缩写。在这种情况下，比较和排序是基于数字字符编码值。
  • ASCII 属性是 CHARACTER SET latin1 的缩写。
  • UNICODE 属性是 CHARACTER SET ucs2 的缩写。

字符列比较和排序基于分配给列的排序规则。对于 CHAR，VARCHAR，TEXT，ENUM 和 SET 数据类型，您可以使用二进制（_bin）排序规则或 BINARY 属性来声明列，可以使比较和排序时使用底层字符编码值而不是词法排序。

文本字符串类型

[NATIONAL] CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]
范围：0-255 字节
定长字符串，存储时始终用空格填充在字符串右侧，使字符串达到 M 指定的字符个数。如果省略 M，则默认为 1。
除非启用了 PAD_CHAR_TO_FULL_LENGTH SQL 模式，否则检索 CHAR 值时，尾随的空格会被删除。
CHAR 是 CHARACTER 的缩写。NATIONAL CHAR（或其简写：NCHAR）是用于定义 CHAR 列应使用某些预定义字符集的标准 SQL 方法。MySQL 使用 utf8 作为这个预定义的字符集。Section 10.1.3.7, “The National Character Set”
CHAR BYTE 数据类型是 BINARY 数据类型的别名。这是为了兼容。
MySQL 允许您创建一个 CHAR(0)类型的列。这主要是当必须兼容一个依赖于已存在的列但实际不使用其值的旧应用程序时。当需要一个可以只有两个值的列：定义为 CHAR(0)的列只占一位，只能使用 NULL 和”（空字符串）时，CHAR(0)也是非常有用的。

[NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]
范围：0-65535
一个可变长度的字符串。M 表示字符的最大列长度。M 的范围为 0〜65,535。VARCHAR 的有效最大长度受最大行大小（65,535 字节，在所有列之间共享）和所使用的字符集影响。例如，utf8 字符每个字符最多可能需要三个字节，因此使用 utf8 字符集的 VARCHAR 列可以被声明为最多 21,844 个字符。参见Section C.10.4, “Limits on Table Column Count and Row Size”
MySQL 将 VARCHAR 值存储为 1 字节或 2 字节长度前缀加数据。长度前缀表示值中的字节数。如果值不超过 255 个字节，则 VARCHAR 列使用一个长度字节，如果值可能需要超过 255 个字节，则使用两个长度字节。
MySQL 遵循标准 SQL 规范，不会从 VARCHAR 值中删除尾随空格。
VARCHAR 是 CHARACTER VARYING 的缩写。NATIONAL VARCHAR（简写：NVARCHAR）是用于定义 VARCHAR 列应使用某些预定义字符集的标准 SQL 方法。MySQL 使用 utf8 作为这个预定义的字符集。Section 10.1.3.7, “The National Character Set”。

TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
最大长度为 255 个字符的 TEXT 列。如果值包含多字节字符，则有效最大长度会变小。TINYTEXT 使用 1 字节长度的前缀来存储值中的字节数。

TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]
范围：65535 个字符。
如果值包含多字节字符，则有效最大长度会变小。TEXT 值使用 2 字节长度的前缀来表示值中的字节数。
M 用于指定长度。如果指定 M，MySQL 将创建足够容纳 M 个字节长的值的最小的 TEXT 类型的列。

MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
范围：16,777,215 个字符。
如果值包含多字节字符，则有效最大长度会变小。MEDIUMTEXT 值使用 3 字节长度的前缀来表示值中的字节数。

LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
范围：4,294,967,295 或 4GB 个字符。如果值包含多字节字符，则有效最大长度会变小。
LONGTEXT 列的有效最大长度取决于 C/S 协议和可用内存中配置的最大数据包大小。LONGTEXT 使用 4 字节长度的前缀来存储值中的字节数。

二进制字符串类型

BINARY(M)
BINARY 类型与 CHAR 类型相似，但存储二进制字节字符串而不是非二进制字符串。M 表示列长度（以字节为单位）。

VARBINARY(M)
VARBINARY 类型类似于 VARCHAR 类型，但存储二进制字节字符串而不是非二进制字符串。M 表示以字节为单位的最大列长度。

TINYBLOB
最大长度为 255 字节的 BLOB 列。TINYBLOB 使用 1 字节长度的前缀来存储值中的字节数。

BLOB[(M)]
最大长度为 65535 字节的 BLOB 列。使用两字节的前缀来存贮值的长度。
M 用于指定长度。如果指定 M，MySQL 将创建足够容纳 M 个字节长的值的最小的 BLOB 类型的列。

MEDIUMBLOB
最大长度为 16777215 字节的 BLOB 列。使用两字节的前缀来存贮值的字节数。

LONGBLOB
范围：4294967295 或 4GB
LONGBLOB 列的有效最大长度取决于 C/S 协议和可用内存中配置的最大数据包大小。LONGBLOB 使用 4 字节长度的前缀来存储值中的字节数。

ENUM(‘value1′,’value2’,…) [CHARACTER SET charset_name] [COLLATE collation_name]
枚举。字符串对象，它只能从值’value1’，’value2’，…，NULL或特殊”错误值中选择一个值。ENUM 值内部表示为整数。
ENUM 列最多可以有 65535 个不同的元素。（实际限制小于 3000.）在 TABLE 的被视为一组的 ENUM 和 SET 之间，可以有不超过 255 个唯一元素列表定义。有关限制参考：Section C.10.5, “Limits Imposed by .frm File Structure”

SET(‘value1′,’value2’,…) [CHARACTER SET charset_name] [COLLATE collation_name]
可以具有零个或多个值的字符串对象，每个值都必须从值’value1’，’value2’的列表中选择… SET 值在内部表示为整数。
SET 列最多可以有 64 个不同的成员。一个表可以在被认为是一个组的 ENUM 和 SET 列之间不超过 255 个唯一的元素列表定义。

常用字符类型操作

字符串截取（SUBSTR、SUBSTRING）

MySQL SUBSTR() function – w3resource 

SUBSTR(str, pos, len)

注意事项：参数 pos 是从 1 开始，而非 0 开始；

字符替换

MySQL REPLACE() function – w3resource
regex – How to do a regular expression replace in MySQL? – Stack Overflow 

REPLACE("str or colname", find_string, replace_with)

-- 在 MariaDB 或 MySQL 8.0.4 中，可以使用 REGEXP_REPLACE 函数
SELECT REGEXP_REPLACE("stackoverflow", "(stack)(over)(flow)", '\\2 - \\1 - \\3')

编码 / 解码（加密）

MySQL decode() – w3resource

这里的编码解码并非 URL 编码解码，而是用于数据加密。 

SELECT ENCODE('mytext','mykeystring');

匹配大小写

regex – How to check for uppercase letters in MySQL? – Stack Overflow 

...
WHERE
    BINARY colname = "STRING WITH UPPERCASE"

判断是否包含某个字符串

-- 使用 INSTR 查找
mysql> SELECT INSTR('foobarbar', 'bar');
        -> 4
mysql> SELECT INSTR('xbar', 'foobar');
        -> 0

-- 与 INSTR 类似，仅是参数顺序的变化
mysql> SELECT LOCATE('bar', 'foobarbar');
        -> 4
mysql> SELECT LOCATE('xbar', 'foobar');
        -> 0

-- 从第五个位置开始查找
mysql> SELECT LOCATE('bar', 'foobarbar', 5);
        -> 7

参考文献

MySQL 5.7 Reference Manual/Data Types
https://dev.mysql.com/doc/refman/5.7/en/spatial-extensions.html
https://dev.mysql.com/doc/refman/5.7/en/json.html
database – Maximum length for MySQL type text – Stack Overflow
MySQL :: MySQL 8.0 Reference Manual :: 12.8 String Functions and Operators/LOCATE
MySQL :: MySQL 8.0 Reference Manual :: 12.8 String Functions and Operators/INSTR

Filed under: K4NZDROID - @ 9:17 PM