Mysql入门系列：MYSQL表达式求值和MYSQL类型转换

; 2.4 表达式求值和类型转换 ; MySQL允许编写包括常量、函数调用和表列引用的表达式。这些值可利用不同类型的运算符进行组合，诸如算术运算符或比较运算符。表达式的项可用圆括号来分组。表达式在SELECT 语句的列选择列表和WHERE 子句中出现得最为频繁，如下所示： 所选择的每列给出了一个表达式，如WHERE 子句中所示的那样。表达式也出现在DELETE 和UPDATE语句的WHERE 子句中，以及出现在INSERT 语句的VALUES( ) 子句中。 ; 在MySQL遇到一个表达式时，它对其求值得出结果。例如， (4 * 3)/(4 - 2) 求值得6。表达式求值可能涉及类型转换。例如， MySQL在数960821用于需要日期值的环境时，将其转换为日期“ 19 9 6 - 0 8 - 2 1”。本节讨论怎样编写MySQL的表达式，以及在表达式求值中MySQL所使用的类型转换规则。每个MySQL的运算符都介绍过了，但MySQL有那么多的函数，我们只接触过几个。每个运算符和函数的进一步介绍可参阅附录C。 ; 2.4.1撰写表达式 ; 表达式可以只是一个简单的常量，如： ; 0 数值常量 ; “a b c” 串常量 ; 表达式可以进行函数调用。有的函数需要参数（圆括号中有值），而有的不需要。多个参数应该用逗号分隔。在调用一个函数时，参数旁边可以有空格，但在函数名与圆括号间不能有空格。下面是一些函数例子：NOW( ) 无参数函数 ; STRCMP (“a b c”, “d e f”) 有两个参数的函数 ; STRCMP( “a b c”, “d e f”) 参数旁边有空格是合法的 ; STRCMP (“a b c”, “d e f”) 函数名后跟空格是不合法的 ; 如果函数名后有一个空格， MySQL的分析程序可能会将函数名解释为一个列名（函数名不是保留字，如果需要的话，可将它们用作列名）。其结果是出现一个语法错误。表达式中可使用表列。最简单的情形是，当某个列所属的表在上下文中是明确的，则可简单地给出列名对该列进行引用。下面的每个SELECT 语句中惟一地出了一个表名，因此，列的引用无歧义： 如果使用哪个表的列不明确，可在列名前加上表名。如果使用哪个数据库中的表也不明确的话，可在表名前加上数据库名。如果只是希望意思更明显，也可以在无歧义的上下文中利用这种更为具体的表示形式，如： 总之，可以组合所有这些值以得到更为复杂的表达式。 ; 1. 运算符的类型 ; MySQL有几种类型的运算符，可用来连接表达式的项。算术运算符，如表2-15 所示，一般包括加、减、乘、除以及模运算符。在两个操作数都是整数时，“+”、“-”和“ *”算术运算用BIGINT（64 位）整数值来完成。而在结果预期为一个整数时，“/”和“%”也是用BIGINT（64 位）整数值来完成的。应该认识到，如果某个运算涉及更大的值，如结果超过64 位，其结果不可预料。 逻辑运算符如表2 - 16所示，对表达式进行估计以确定其为真（非零）或假（零）。MySQL包含有C 风格的“& &”、“| |”和“!”运算符，可替换A N D、OR 和N O T。要特别注意“| |”运算符，ANSI SQL 指定“| |”作为串连接符，但在MySQL中，它表示一个逻辑或运算。如果执行下面的查询，则返回数0： MySQL为进行运算，将“ a b c”和“d e f”转换为整数，且两者都转换为0, 0与0进行或运算，结果为0。在MySQL中，必须用CONCAT (“a b c”, “d e f”) 来完成串的连接。 位运算符如表2-17 所示，完成按位“与”和“或”，其中结果的每一位按两个操作数的对应位的逻辑AND 或OR 求值。还可以进行位的左移或右移。位运算用BIGINT（64 位）整数值进行。 比较运算符如表2-18 所示，其中包括测试相对大小或数和串的顺序的运算符，以及完成模式匹配和测试NULL 值的运算符。“< = >”运算符是MySQL特有的，在MySQL3.23版本中引入。 自MySQL3.23版本起，可使用B I N A RY 运行符，此运算符可用来将一个串转换为一个二进制串，这个串在比较中是区分大小写的。下列的第一个比较是不区分大小写的，但第二个和第三个比较是区分大小写的： 没有相应的NOT BINARY 计算。如果希望使一个列既能在区分大小写又能在不区分大小写的环境中使用，则应该利用不区分大小写的列并对希望区分大小写的比较使用BINARY。对于利用二进制串类型（CHAR BINARY、VARCHAR BINARY 和BLOB 类型）定义的列，其比较总是区分大小写的。为了对这样的列类型实现不区分大小写的比较，可利用UPPER( ) 或LOWER( ) 来转换成相同的大小写： 对于不区分大小写的串比较，有可能把多个字符认为是相等的，这取决于所用的字符集。例如“e”和“é”对于比较和排序操作可能是相同的。二进制（区分大小写）比较利用字符的ASCII 值来完成。 ; 模式匹配允许查找值而不必给出精确的直接值。MySQL利用LIKE 运算符和通配符“%”（匹配任意的字符序列）和“ _”（匹配任意单个字符），提供SQL 的模式匹配。MySQL还基于类似于诸如g r e p、sed 和vi 等UNIX 程序中所用的REGEXP 运算符和扩展正规表达式，提供模式匹配。为了完成模式匹配，必须使用这些模式匹配运算符中的某一个；不能使用“ =”。为了进行相反的模式匹配，可使用NOT LIKE 或NOT REGEXP。 ; 除了使用的模式运算符和模式字符不同外，这两种模式匹配还在以下重要的方面存在差异： ; ■ 除非至少有一个操作数为二进制串，否则LIKE 是不区分大小写的。REGEXP 是区分大小写的。（在MySQL3.23.4 以后的版本中，除非至少有一个操作数是二进制串，否则REGEXP 是不区分大小写的。） ; ■ 仅当整个串匹配，SQL 才是模式匹配的。仅当相应的模式在串中某一处出现，正规表达式才匹配。用于LIKE 运算符的模式可以包括“%”和“_”通配符。例如，模式“ F r a n k %”与任何以“F r a n k”起头的串匹配： 通配符“%”与任何串匹配，其中包括与空字符序列匹配，因此“ Frank %”与“ Frank”匹配： 这也表示模式“%”与任何串匹配，其中包括与空串匹配。但是，“%”不与NULL 匹配。事实上，具有NULL 操作数的任何模式匹配都将失败： MySQL的LIKE 运算符是不区分大小写的，除非它至少有一个操作数是二进制串。因此，缺省时“ Frank %”与串“ Frankly”和“frankly”匹配，但在二进制比较中，它只与其中之一匹配： 这不同于ANSI SQL 的LIKE 运算符，它是区分大小写的。通配符可在模式中任何地方给出。“% bert”与“ Englebert”、“Bert”和“Albert”匹配。“% bert %”也与所有这些串匹配，而且还与如像“ Berthold”、“Bertram”、和“Alberta”这样的串匹配。LIKE 所允许的另一个通配符是“ _”，它与单个字符匹配。“_ _”与三个字符的串匹配。“c _ t”与“c a t”、“c ut”甚至“c _ t”匹配（因为“_”与自身匹配）。为了关掉“%”或“ _”的特殊含义，与这些字符的直接实例相匹配，需要在它们前面放置一个斜杠（“ %”或“ _”），如：　 MySQL的另一种形式的模式匹配使用了正规表达式。运算符为REGEXP 而不是L I K E（RLIKE 为REGEXP 的同义词）。最常用的正规表达式模式字符如下：‘.’与任意单个字符匹配： ‘[ . . . ]’与方括号中任意字符匹配。可列出由短划线‘ -’分隔的范围端点指定一个字符范围。为了否定这种区间的意义（即与未列出的任何字符匹配），指定‘ ^’作为该区间的第一个字符即可： ‘*’表示“与其前面字符的任意数目的字符匹配”，因此，如‘ x *’与任意数目的‘ x’字符匹配，例如： “任意数目”包括0个实例，这也就是为什么第二个表达式匹配成功的原因。‘^pat’和‘pat$’固定了一种模式匹配，从而模式pat 只在它出现在串的前头时匹配，而‘ ^pat$’只在pat 匹配整个串时匹配，例如： REGEXP 模式可从某个表列中取出，虽然如果该列包含几个不同的值时，这样做比常量模式慢。每当列值更改时，必须对模式进行检查并转换成内部形式。MySQL的正规表达式匹配还有一些特殊的模式字符。要了解更详细信息请参阅附录C。 ; 2. 运算符的优先级 ; 当求一个表达式的值时，首先查看运算符以决定运算的先后次序。有的运算符具有较高的优先级；例如，乘和除比加和减的优先级更高。下面的两个表达式是等价的，因为“ *”和“/”先于“+”和“-”计算： 下面列出了运算符的优先级，从高到低。列在同一行中的运算符具有相同的优先级。优先级较高的运算符在优先级较低的运算符之前求值。 可用圆括号来忽略运算符的优先级并改变表达式的求值顺序，如： 3. 表达式中的NULL 值 ; 请注意，在表达式中使用NULL 值时，其结果有可能出现意外。下列准则将有助于避免出问题。如果将NULL 作为算术运算或位运算符的一个操作数，其结果为NULL： 如果将NULL 用于逻辑运算符，NULL 被认为是假： NULL 作为任意比较运算符的操作数，除< = >、IS NULL 和IS NOT NULL 运算符（它们是专门扩展来处理NULL 值的）外，将产生一个NULL 结果。如： 如果给函数一个NULL 参数，除了那些处理NULL 参数的函数外，一般返回一个NULL结果。例如，IFNULL( ) 能够处理NULL 参数并适当地返回真或假。STRCMP( ) 期望一个非NULL 的参数；如果它发现传给它的是一个NULL 参数，则返回NULL 而不是真或假。在排序操作中，NULL 值被归到一起。在升序排序中， NULL 将排在所有非NULL 值之前（包括空串），而在降序排序中，NULL 将排在所有非NULL 值之后。 ; 2.4.2 类型转换 ; MySQL根据所执行的操作类型，自动地进行大量的类型转换，任何时候，只要将一个类型的值用于需要另一类型值的场合，就会进行这种转换。下面是需要进行类型转换的原因： ; ■ 操作数转换为适合于某种运算符求值的类型。 ; ■ 函数参数转换为函数所需的类型。 ; ■ 转换某个值以便赋给一个具有不同类型的表列。下列表达式涉及类型转换。它由加运算符“ +”和两个操作数1和“2” 组成： ; 1+'2' ; 其中操作数的类型不同，一个是数，另一个是串，因此， MySQL对其中之一进行转换以便使它们两个具有相同的类型。但是应该转换哪一个呢？因为，“+”是一个数值运算符，所以MySQL希望操作数为数，因此，将串“ 2”转换为数2。然后求此表达式的值得出3。再举一例。C O N C AT( ) 函数连接串产生一个更长的串作为结果。为了完成此工作，它将参数解释为串，而不管参数实际是何类型。如果传递给C O N C AT( ) 几个数，则它将把它们转换成串，然后返回这些串的连接，如： 如果作为表达式的组成部分调用C O N C AT( )，可能会进行进一步的类型转换。考察下列表达式及其结果： CONCAT(1, 2, 3) 产生串“12 3”。表达式“12 3”/10 转换为12 3 / 10，因为除是一个算术运算符。这个表达式的结果的浮点形式为12 . 3，但REPEAT( ) 需要整数的重复计数值，所以进行整除得12。然后，REPEAT (‘X’, 12) 产生一个含有12个‘X’ 字符的结果串。一般原则是，MySQL尽量将值转换为表达式所需要的类型，尽量避免由于值的类型不对而导致错误。根据上下文， MySQL将在三种通用类型（数、串或日期与时间）之间进行值的转换。但是，值不能总是可以从一种类型转为另一种类型。如果被转换值不是给定类型的合法值，则此转换失败。将如“a b c” 这样不像数的东西转换为数，则结果为0。将不像日期或时间的东西转换为日期或时间类型结果为该类型的“零”值。例如，将串“a b c” 转换为日期结果为“零”日期“ 0 0 0 0 - 0 0 - 0 0”。而任何值都可以处理为串，因此，一般将某个值转换为串不会产生问题。 ; MySQL也进行一些微小的类型转换。如果在整型环境中使用一个浮点值，此值将被转换，转换时进行四舍五入。也可以进行相反的工作；一个整数用作浮点数也不会有问题。除非其内容显示表示一个数，否则十六进制常数一般作为串处理。在串上下文中，每对十六进制数字转换为一个字符，其结果作为串。下面是一些转换的样例： 相同的解释原理也应用到比较上；除非与其比较的是一个数，否则十六进制常量按串对待，例如： 某些运算符可将操作数强制转换为它们所要的类型，而不管操作数是什么类型。例如，算术运算符需要数，并按此对操作数进行转换，参考如下运算： MySQL不对整个串进行寻找一个数的查找；它只查看串的起始处。如果一个串不以数作为前导部分，其转换结果为0。 请注意，MySQL的串到数的转换规则自3 . 2 3版以后已经改变了。在该版本以前，类似于数的串被转换为四舍五入的整数值。自3.23 版后，它们转换为浮点值，例如： 逻辑和位运算符比算术运算符要求更为严格。它们不仅希望操作数为数，而且还要求是整数。这表示一个浮点数，如. 3，不被视为真，虽然它是非零的；这是因为在转换为整数时，. 3已经转换为0了。在下面的表达式中，除非各操作数有一个至少为1的值，否则各操作数不被认为是真。 这种转换也出现在IF( ) 函数中，此函数要求第一个参数为整数。为了恰当地对浮点值进行测试，最好是利用明确的比较。否则，小于1的值将被认为是假，例如： 模式匹配运算符要求对串进行处理。这表示可将MySQL的模式匹配运算符用于数，因为MySQL会在试图进行的匹配中将它们转换成串。例如： 大小比较运算符（“<”、“< =”、“=”等等）是上下文相关的；即，它们根据操作数的类型求值。下面的表达式从数值上对操作数进行比较，因为操作符两边都是数。

2 < 11-> 1; ; ; 下面的表达式涉及串比较，因为其两边的操作数都是串： '2' < '11' -> 0 ; 在下面的比较中，类型是混合的，因此，MySQL按数比较它们。结果是两个表达式都为真： 在各个比较中，MySQL根据下列规则对操作数进行转换： ; ■ 除了“< = >”运算符外，涉及NULL 值的比较其值为NULL（除NULL <=> NULL 为真外，“< = >”与“=”相同）。 ; ■ 如果两个操作数都是串，则按串进行字典顺序的比较。串比较利用服务器上有效的字符集进行。 ; ■ 如果两个操作数都为整数，则按整数进行数的比较。 ; ■ 不与数进行比较的十六进制常量按二进制串进行比较。 ; ■ 如果其中有一个操作数为TIMES TAMP 或DATETIME 值而另一个为常量，则按TIMES TAMP 值进行比较。这样做将使比较对ODBC 应用更好。 ; ■ 否则，两个操作数将按浮点值进行数的比较。注意，这包括一个串与一个数进行比较的情况。其中串被转换为数，如果该串转换后不是一个数，则结果为0。例如，”14 . 3”转换为14 . 3，但“L 4 . 3” 转换为0。 ; 1. 日期与时间的解释规则 ; MySQL按表达式的环境将串和数自由地转换为日期和时间值，反之亦然。日期和时间值在数值上下文中转换为数；数在日期或时间上下文中转换为日期或时间。在将一个值赋予一个日期或时间列时，或在函数需要一个日期或时间值时，进行转换为日期或时间值的转换。如果表my_table 含有一个DATE 列date _ col，下列语句是等价的： TO _ DAYS( ) 函数的参数在下面三个表达中为相同的值： 2. 测试并强制进行类型转换 ; 为了了解表达式中类型转换是怎样进行的，用mysql程序发布一条对表达式求值的SELECT 语句如下： 正如您所想像的那样，笔者在撰写本章时，做了不少这种比较。测试表达式的求值对于诸如DELETE 或UPDATE这种修改记录的语句极为重要，因为需要保证只涉及所需涉及的行。检查表达式的一个办法是，预先执行一条具有准备用于DELETE 或UPDATE语句的相同WHERE 子句，以验证该子句选择的行是正确的。假如表my_table 具有一个含有下列值的CHAR 列char _ col： 原来的打算大概是想删除包含值“0 0” 的那两行。但实际作用是删除了所有的行。之所以这样是由于MySQL的比较规则在起作用。char_col 为一个串列，但00 没有用引号括起来，因此，它被作为数对待了。按MySQL的比较规则，涉及一个串与一个数的比较按两个数的比较来求值。随着DELETE 查询的执行，char_col 的每个值被转换为0，”0 0”也被转换为0，因此，所有不类似数的串都转换成0。从而，对于每一行， WHERE 子句都为真，因此，DELETE 语句清空了该表。显然，这是一种在执行DELETE 前，应该用SELECT 语句对WHERE 子句进行测试的情况，这样将会示出表达式所选择的行太多了。如下所示： 如果不能肯定某个值的使用方式，可以利用MySQL的表达式求值机制将该值强制转换为特定的类型： ; ■ 增加+ 0 或+ 0.0 到某项上以强制转换到一个数值： 3. 超范围值或非法值的转换 ; 超范围值或非法值的转换的基本原则为：无用输入，无用输出。如果不在存储日期前对其进行验证，那么可能会得到不喜欢的东西。下面给出一些MySQL处理超范围值或不合适值的一般原则，这些内容曾经在前面介绍过： ; ■ 对于数值或T I M E列，超出合法范围的值被剪裁为相应取值范围的最接近的数值并作为结果值存储。 ; ■ 对于非ENUM 或SET 的串列，太长的串被截为适合该列存储的最大长度的串。ENUM 或SET 列的赋值依赖于定义列时给出的合法值。如果赋予ENUM 列一个未作为枚举成员给出的值，将会赋予一个错误成员（即，对应于零值成员的空串）。如果赋予SET 列一个包含未作为集合成员给出的子串的值，那么，那些未作为集合成员给出的子串将被删除，并将剩余成员构成的值赋给该列。 ; ■ 对于日期或时间列，非法值被转换为该类型适当的“零”值（参阅表2 - 11）。对于非TIME 的日期和时间列，超出取值范围的值可转换为“零”值、NULL或某种其他的值（换句话说，结果是不可预料的）。 ; 这些转换都将作为ALTER TABLE、LOAD DATA、UPDATE和多行INSERT 语句的警告信息报告。在mysql客户机中，这些信息显示在查询报告的状态行上。在编程语言中，可通过某些其他手段取得这个信息。如果使用的是MySQLC API，那么可调用mysql_info( ) 函数来获得这个信息。对于Perl DBI API，可利用数据库连接的mysql_info 属性。所提供的这个信息是警告信息的次数计数。为了知道更改了哪些行，可发布一条SELECT ... INTOOUTFILE 查询，并将结果与原始行进行比较。