MySQL的值类别：数值、字符串、时态值(日期/时间)、空间值、布尔值、NULL

一、数值

• 数值是指诸如48、193.62或-2.37E12之类的值。
• MySQL所能所能识别的数字包括：整数（无小数位）、定点数、浮点数、位域值（bit-field value）。

• 当你指定某个数字时，请不要将逗号作为分隔符包括在内。例如，12345678.90是合法的， 但 12,345,678.90是非法的。

精确值数和近似值数

• MySQL支持对精确值数的精确运算，以及对近似值数的近似运算。
• 精确值数在使用时与指定值是完全一致的。精确值包括整数（如0、14、-382）和带小数点的数（如0.0、38.5、-18.2）。
• 整数可以表示成十进制或十六进制两种格式：
  • 在十进制格式下，每个整数由一个不包含小数点的数字序列构成。
  • 十六进制值会被默认为字符串，但在进行数值运算的时候，十六进制常量会被视为64位的整数 。例如，0x10即为十进制数16。
• 小数部分的精确值由3个部分组成：一个数字序列、一个小数点和另一个数字序列。小数点前后的数字序列可以有一个为空，但不能同时为空。
• 近似值是采用科学计数法表示的浮点数，它们带有一个底数和一个指数。
  • 具体表示方法为：在整数或浮点数的后面紧跟着字母e或E；接着是一个正号(+)或负号(-)，然后是一个表示指数 的整数 。底数和指数的正负号可以任意组合，如1.58E5、-1.58E5、1.58E-5、-1.58E-5。
  • 十六进制数不能用科学计数法来表示，这是因为指数部分的开始字母“e”，也是一个合法 的十六进制数字，这会产生二义性。
• 你可以在任何一数值的前面加上一个正号(+)或负号(-)，以此表明它是一个正值或负值 。
• 用精确值计算得到的结果也是精确的，只要没有超出那些数值的精度范围 ，就不会丢失精 度。例如，你不能原封不动地把1.23456插到只允许两位小数的列里。近似值的计算则是近似的，而且会有舍入误差。
  • 在计算表达式时，MySQL会根据以下规则来决定是使用精确计算还是使用近似计算。
  • 只要表达式里有近似值 ，那么它便会被当作浮点(近似)表达式来进行计算。
  • 如果表达式只包含整数精确值 ，那么它会以BIGINT (64位)精度来进行计算。
  • 如果表达式只包含精确值 ，但其中有的值带有小数部分，那么它会以具有65位精度的DECIMAL算法来进行计算。
  • 在表达式里，如果存在字符串必须转换成一个数才能进行计算的情况 ，那么该字符串会 被转换成一个双精度浮点值 。因此，该表达式会按照前面的规则进 行近似计算。

位域值

• 位域值可以写成b'val'或0bval，其中，val由一个或多个二进制数字(0或1)构成。
• 例如，b'1001'和0b1001即为十进制数9。
• 在结果集里，BIT值会被显示为一个二进制串，不过其输出格式并不太好。让它加上零或者使用CAST()函数 ，可以将其转换为一个整数 :

SELECT b'1001' + 0, CAST(b'1001' AS UNSIGNED);

二、字符串

• 字符串两端的引号既可以是单引号 ，也可以是双引号。
• 请尽量使用单引号，具体原因有两个：
  • SQL语言标准规定使用单引号 ，因此使用单引号字符串的语句，能够更好地移植到其他数据库 引擎。
  • 如果启用了SQL模式ANSI_QUOTES，那么MySQL会将双引号处理成将标识符引起来的符号 ，而不会把它只当成将字符串引起来的符号 。也就是说 ，双引号里的值必须是数据库名或表名。请看下面这条语句：
    • 若启用了ANSI_QUOTES模式，那么此语句将从 president表里选取last_name列的值 。
    • 若未启用ANSI_QUOTES模式，那么此语句将为该表的每行选取一次字符串"last_name"。

SELECT "last_name" FROM president;

• 在后面的示例里，会假设未启用ANSI_QUOTES模式，即双引号是一个 字符串引号符。 

转义字符

• MySQL能够识别出字符串里用来代表特殊字符的转义序列，如下图所示。

• NUL字节与SQL语言中的NULL值是不同的：NUL代表的是零值字节，而NULL值代表的是“没有值 ”。
• 转义序列是区分大小写的。所有未列在此表里的字符，即使其前面加上一个反斜线字符，也仍然会被解释为该字符本身。例如，\t是一个制表符，而\T是一个普通的T字符。
• 如果需要取消反斜线字符的特殊含义 ，并把它当作一个普通字符，那么请启用SQL的NO_BACKSLASH_ESCAPES模式。
• 从上图可以看出，你可以用反斜线序列来将单引号或双引号转义，但实际上还有几种方法可以在字符串内嵌入引号 。
• ①如果嵌入的引号与字符串的引号相同，则重复两次即可：

'I can''t'
"He said, ""I told you so."""

• ②如果嵌入的引号与字符串的引号不相同，那么不用重复即可嵌入：

"I can't"
'He said, "I told you so."'

• ③嵌入的引号使用反斜线进行转义；此办法不受字符串引号的限制：

'I can\'t'
"I can\'t"
"He said, \"I told you so.\""
'He said, \"I told you so.\"'

十六进制记法

• 在使用引号书写字符串值时 ，还有两种形式的十六进制记法可供选择。
• 第一种是使用标准的SQL记法如下所示：
  • 其中val由多对十六进制数字(包括0~9和a~f)组成。
  • 例如，X'0a'即为十进制的10，而X'ffff'即为十进制的65535。

X'val'

• 前导字符“X”和字符串里的十六进制数字字符(a~f)都不区分大小写。例如：

SELECT X'4A', x'4a';

• 在字符串上下文中，每两个十六进制数字会被解释为一个8位数字字节值，其取值范围是0〜255；而其结果会被当作一个字符串。在数字上下文中，十六进制常量会被当成一个数。下列语句展示了十六进制常量在两种上下文中的解释情况 :

SELECT X'61626364', X'61626364'+0;

• 第二种记法是以“0x”开头 ，后面跟着一个或多个十六进制数字。前缀“0x”需要区分大小写 。“0x0a”和 “0x0A”是合法的十六进 制数 值；而 “0x0a”和 “0X0A”则 是非法的。
• 与X'val'记法的情况类似，0x值会被默认解释为字符串，但在数值上下文中，则可能被用作数字：

SELECT 0x61626364, 0x61626364+0;

• X'val'记法要求构成val的数字个数为偶数。像X'a'这样的值是非法的。如果用0x记法写的十六进制值只有奇数个十六进制数字，那么MySQL将会在其前面增加一个字符“0”。例如，0xxa会将被视为0x0a。

字符串类型与字符集支持

• 字符串值一般可以分为两类：
  • 二进制串：二进制串是一组字节序列。对这些字节的解释不牵涉任何字符集概 念。二进 制串没有特殊的比较或排序属性。比较操作是基于各字节的数值逐个字节实现的。所有字节都有意义 ，甚至包括结尾的空格。
  • 非二进制串：非二进制串是一个字符序列。
    • 每一个非二进制串都与字符集相关 ，字符集决定了：哪些字符可以用；MySQL会如何解释字符串的内容。
    • 每个字符集都有一种或多种排序规则。字符串所使用的排序规则决定了字符在字符集里的先后顺序，而这会对比较操作产生影响 。默认的字符集和排序规则分别为latin1和latin1_swedish-ci。
    • 非二进制串里的尾部空格不会参与比较 ，但TEXT类型除外。由于基于索引的比较 会在尾部补足一些空格，因此当你试图往一个具有唯一性的TEXT索引里插入某个值 时，如果它与另一个已有值只是在尾部空格的个数上不同，那么会导致一个“关键字重复”错误。

• 字符单位在占用存储空间方面存在差异 。对于像latin1这样的单字节字符集，每个字符只占用一个字节空间，而对于那些多字节字符集，有的是部分，有的甚至是全部字符都需要占用一个字节以上的空间。例如，MySQL里的Unicode字符集是多字节的。ucs2是双字节字符集，其每个字符需要两个字节来存储。utf8是变长多字节字符集，其每个字符需要占用1〜3个字节空间。有的Unicode字符集还会占到每字符4个字节的空间。

• 下面的语句可以分别查看当前数据库提供的字符集以及对应的排序规则：

SHOW CHARACTER SET;
SHOW COLLATION;

• 排序规则的后缀由字符集名、语言名和一个附加的后缀构成。例如，utf8_icelandic_ci是Unicode字符集utf8的一种排序规则 ，其比较操作遵从的是冰岛(icelandic)排序规则，并且被比较字符都要区分大小写。
• 排序规则名里的后缀具有下列含义：
  • _ci表明排序规则不区分大小写。
  • _cs表明排序规则要区分大小写。
  • _bin表明这是一种二进制排序规则。也就是说，比较操作是基于数字字符编码值进行的，与语言无关 。因此，_bin的排序规则名里不包括语言名，如latin1_bin和utf8_bin。
• 二进制串和非二进制串有着不同的排序特性：
  • 二进制串是逐字节进行比较的，其结果只取决于每个字节的数值大小。因此，二进制串看起来像是区分大小写的(如'abc' <> 'ABC')，但这只是因为同一个字符的大写和小写形式有着不同的字节数值而已。二进制串其实没有大小写的概念。区分大小写是排序规则的一项功能，只适用于字符(非二进制)字符串。
  • 非二进制串是按字符进行比较的，每一个字符的相对值取决于当前所用字符集的排序规则。大多数排序规则都把同一个字符的大小写形式设定为相同的排序值 ，所以非二进制串的比较 一般情况下不区分大小写。当然，这一定论不适用于那些区分大小写的排序规 则或者是二进制排序规则。
• 因为排序规则应用于比较和排序，所以它们会对以下操作造成影响：
  • 比较运算符：<、<=、=、<>、>=、>和LIKE。
  • 排序：ORDER BY、MIN()和MAX()。
  • 分组：GROUPBY和DISTINCT。
• 你可以使用函数CHARSET()或COLLATION()来确定某个字符串的字符集和排序规则 。
• 引号里的字符串会根据服务器的当前设置来解释。默认的字符集和排序规则分别为latin1和latin1_swedish-ci。不过 ，当你将环境变量LANG或LC_ALL设置为某个指定语言区域时 ，mysql和其他的客户程序可以检测到它们，并进行相应的调整。（详情请看下面"字符串相关的系统变量"）
• 默认情况下，MySQL把十六进制常量当作二进制串对待：

SELECT CHARSET(X'0123'), COLLATION(X'0123');

• 有两种记法约定可用于将某个字符串常量强制解释为某种指定的字符集。
• 第一种记法如下所示：其中，charset是某种被支持的字符集名。_charset记法称为字符集引导符 (character set introducer)。紧跟引导符后面的字符串，既可以用引号形式，也可以是一个十六进 制值

_charset str

• 下面示例演示的是如何将字符串解释为latin2或utf8字符集：对于引号里的字符串，字符集引导符和字符串之间的空白是可选的。对于以十六进制值形式给出的字符串，这个空白是必需的。

_latin2 'abc'
_latin2 X'616263'
_latin2 0x616263
_utf8   'def'
_utf8   X'646566'
_utf8   0x646566

• 第二种记法是N'str'，它等价于_utf8'str'。请注意，在N(不区分大小写 )的后面必须紧跟一个引号形式的字符串，其间不能有任何空白。
• 引导符记法可用于引号形式的字符串或十六进制常量，但不能用于字符串表达式或列值。 不过，可以使用CONVERT()函数将任意字符串转换为另一种指字符集形式的字符串：

CONVERT(str USING charset);

• 引导符和C0NVERT()函数是不一样的。引导符只会改变对字符串的解释，并不会改变字符串 的值(不过，对于多字节字符集，当该字符串包含的字节数不足时，它可能会在其尾部增加一些空格)。而CONVERTO函数则是以某个字符串作为输入参数，并根据给定的字符集生成一 个新的字符串。通过下面的示例可以看出字符集引导符和CONVERT()函数之间的区别，它们都使用了UCS2双字节字符集：

SET @s1=_ucs2 'ABCD';
SET @s2 = CONVERT('ABCD' USING ucs2);

• 假设当前字符集为latin1(—种单字节字符集)。第一条语句将把字符串‘ABCD’中的每一对字符解释为一个双 字节ucS2字符，结果即为包含两个字符的UCS2字符串。第二条语句将把字符串_ABCD'中的每个字符转换为相应的ucs2字符，结果为包含4个字符的ucs2字符串。
• 字符串的“长度”如何定义？这要视具体情况而定。
  • 如果用CHAR_LENGTH()函数来测量， 那么长度的单位是字符。
  • 如果用LENGTH()函数来测量，那么长度的单位是字节。
  • 对包含有多字节字符的字符串来说 ，这两个值是不一样的：

SELECT CHAR_LENGTH(@s1), LENGTH(@s1), CHAR_LENGTH(@s2), length(@s2);

• 这里还有一个更容易让人迷糊的问题 ，即二进制串与使用某种二进制排序规则的非二进制串是不一样的。
  • 二进制串没有字符集的概 念。它会被解释成字节 ，并且比较的是单字节的数字代码 。
  • 使用了二进制排序规则的非二进制串，会被解释成字符，并且比较的是它们的数字字符值 ，这种值通常是基于每个字符多个字节算出的。
• 通过下面这个示例，我们可以看到二进制串和非二进制串在大小写方面的区别。它会先创建一个二进制串和一个使用了某种二进制排序规则的非二进制串，然后把它们传递给UPPER()函数

SET @s1=BINARY 'abcd';
set @s2=_latin1 'abcd' COLLATE latin1_bin;
SELECT UPPER(@s1), UPPER(@s2);

• UPPER()函数为何没有把二进制串转换为大写呢？这是因为二进制串没有任何字符集的概 念，因而无从得知哪些字节值对应着大写或小写字符。如果需要把某个二进制串传递给诸如UPPER()或LOWER()之类的函数 ，那么必须先把它转换为非二进制串：

SELECT @s1, UPPER(CONVERT(@s1 USING latin1));

字符集相关的系统变量

• MySQL服务器有几个系统变量，它们涉及字符集支持的各个方面。其中大部分变 量与字符 集有关 ，其余的与排序规则有关。每个 排序规则变量都有一个相应的字符集变量与之相连 。
• 有些字符集变量表示的是服务器或当前数据库的属性：
  • character_set_system表示的是用于存储标识符的字符集。这始终为utf8。
  • character_set_server和collation_server分别表示服务器的默认字符集和排序规则。
  • character_set_database和collation_database分别表示默认数据库的字符集和排序规则 。这两个变量都是只读的，每当你选择一个默认数据库时，服务器都会自动将它们设置好。如果没有默认数据库，那么它们会被设置为服务器的默认字符集和排序规则。当你创建某个表时，如果没有明确地为其指定字符集和排序规则，那么这些变量便会发挥作用。这时，表的默认设置将会沿用数据库的默认设置。
• 其他的字符集变量将影响客户和服务器之间通信：
  • character_set_client表示的是客户端向服务器发送SQL语句时使用的字符集。
  • character_set_results表示的是服务器向客户端返回结果时使用的字符集。这里所说的“结果”包括数据值和诸如列名之类的元数据。
  • character_set_connection是服务器使用的变量。当服务器接收到来自客户端的语句子符 串时 ， 会将该字符串从character_set_client转换为character_set_ connection，并使用后者的字符集来处理该语句。 (这里有个例外，即对于语句里的所有文字串，如果它 由字符集引导符引导 ，那么它会使用该字符集引导符所表示的字符集来进行解释。)。collatioruconnection适用于语句字符串里的两个文字串值之间的比较。
  • character_set_filesystem表示的是文件系统 字符集。MySQL会用它来解释在SQL语句(如LOAD DATA语句)里代表文件名的文字串。在该文件被打开之前，其文件名字符串会 从character_set_client转换为character_set_filesystem。 此变量的默认值为binary(即不进行转换)。
• 很可能你会发现，绝大多数字符集和排序规则变量都被默认设置成了相同的值。例如，下面的输出即表明：客户端和服务器之间使用了latin1字符集来进行通信。

SHOW VARIABLES LIKE 'character\_set\_%';
SHOW VARIABLES LIKE 'collation\_%';

• 如果某个客户端想使用另一种字符集来与服务器进行通信，那么它需要修改与通信有关的变 量。例如，想要使用utf8字符集，则需要更改3个变量：

SET charcter_set_client = utf8;
SET charcter_set_results = utf8;
SET charcter_set_connection = utf8;

• 不过，使用SET NAMES语句可以更方便地达到同样的效果。下面这条语句与上面的3条SET 语句是等效的：

SET NAMES 'utf8';

• 在设置与通信有关的字符集时会有一个限制，即不能使用ucs2、utf16、utf16le或utf32。

• 有许多客户端程序都支持——default-character-set选项 ，此选项可以获得与SET NAMES语 句相同的效果，达到将你所用通信字符集通知服务器的目的。对于像mysql和nysqladmin这 种标准的MySQL客户端程序，甚至还有一种更简单的方法。将环境变量LANG或 LC_ALL设置成指定某一个语言区域，然后这些客户端会检测这些变量，并进行相应的调整。例如，如果将LC_ALL设置为en_US.UTF-8，那么文字量的默认字符集和排序规则会受到类似下面那样 的影响：

SELECT CHARSET('abcd'),COLLATION('abcd');

• 对于那些成对出现的变量(一个字符集变量和一个排序规则变量)，它们之间的影响关系如下：
  • 在设置字符集变量的同时 ，也会将相关的排序规则变量设置为该新字符集默认的排序规则。
  • 在设置排序规则变量的同时，也会将相关的字符集变量设置为该新排序规则名的第一部分所暗示的那个字符集。
  • 例如，当把character_set_connection变量设置为utf8时 ，会将collaton_connection设置为 utf8_general_ci。将collation_connection变 量设 置为latin1_spanish_ci时，会将character一set_connection设置为latin1。

三、时态值(日期/时间)

• 时态值包括日期值或时间值，如'2012-06-17'或’12:30:43'。MySQL也支持将日期和时间合并 在一起的值，如'2012-06-17 12:30:43'。
• 需要特别注意的是，MySQL是按“年-月-日”的顺序来表示日期的，并且输入值也必须是这样的顺 序。虽然这是标准的SQL格式(也叫“ISO 8601”格式)，但还是会有很多MySQL初学者对此感到不习 惯。
• 可以利用DATE_FORMAT()函数 ，按任意方式来显示日期值，但默认显示格式是先显示年。对于其他格式的输入值，需要使用STR_TO_DATE()函数来进行转换。
• 对于组合后的日期时间值，允许在日期和时间之间加一个字符“T”，但不能用空格，如'2008-12-31T12:00:00'。
• 时间值或日期时间组合值的语法还支持在时间后面紧跟一个小数形式的秒，其中包含有一个小数点和多达6位数字(微秒)的精度，如'12:30:15.0000451'或'2008-06-15 10:30:12.5'。

四、空间值

• MySQL支持空间值，不过仅限于InnoDB、MylSAM、NDB和ARCHIVE这几种引擎。
• 这一功能让我们可以直接表示像点、线和多边形这样的值。这些类型都是每一个OpenGIS规范的实现。关于这些规范的更多信息，可以在 “Open Geospatial Consortium”的官网http://www.opengeospatial.org/上获得。
• 例如，有一个X和Y坐标的值为(10，20)的点，下面语句利用其文本表示形式，创建了一个POINT类型，并把结果赋值给了一个自定义变量：

SET @pt=POINTFROMTEXT('POINT(10 20)');

五、布尔值

• 在表达式里，零会被当成假，任何非零、非NULL的值会被当成真。
• 布尔常量TRUE和FALSE将分别被当作1和0。它们不区分大小写。

六、NULL

• NULL是一种“没有类型的”值。它通常用来表示“无值 ”、“未知值 ”、“缺失值 ”、“超界”、“不适用”和 “不在其中”等。
• 可以将NULL值插到表中，从表里检索它们 ，以及测试某个值是否为NULL。
• 但不能对NULL进行算术运算。如果用它进行算术运算，那么结果将是NULL。此外, 有许多函数 ，在你使用NULL或非法参数调用它们时，也会返回NULL。
  • 值=NULL或者值<>NULL等比较操作返回的结果仍然是NULL（不论真假），因为我们无法得知结果是真还是假。即使NULL=NULL返回的结果也是NULL。
  • 如果想要在表中查找NULL值或者非NULL值，可以使用IS NULL或者IS NOT NULL运算符。关于演示可以参阅javascript:void(0)。
• 也可以使用IF()和IFNULL()来对NULL进行操作，详情可以参阅javascript:void(0)。
• MySQL特有的<=>运算符可以用来比较NULL，这一点与=运算符不同。例如：

SELECT NULL = NULL, NULL <=> NULL;

• 在书写关键字NULL时，不需要加引号 ，也不用区分大小写。MySQL还会把单独的\N (区分大小写 )当作NULL。例如：

SELECT \N,ISNULL(\N);