「MySQL」- String Types(学习笔记)

  CREATED BY JENKINSBOT

类型概述

在某些情况下,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')

匹配大小写

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