MySQL数据类型和运算符(四)

Updated on in MySQL with 0 views and 0 comments

数据类型和运算符

MySQL数据类型介绍

MySQL支持多种数据类型,主要有数值类型日期/时间类型字符串类型

  1. 数值数据类型:包括整数类型TINYINTSMALLINTMEDIUMINTINTBIGINT浮点小数数据类型FLOATDOUBLE定点小数类型DECIMAL
  2. 日期/时间类型:包括YEARTIMEDATEDATETIMETIMESTAMP
  3. 字符串类型:包括CHARVARCHARBINARYVARBINARYBLOBTEXTENUMSET等。字符串类型又分文本字符串二进制字符串

整数类型

  • 数值型数据类型主要用来存储数字。MySQL提供了多种数值数据类型,不同的数据类型提供了不同的取值范围,可以存储的值范围越大,其所需要的存储空间也会越大。MySQL主要提供的整数类型有TINYINTSMALLINTMEDIUMINTINT(INTEGER)BIGINT。整数类型的属性字段可以添加AUTO_INCREMENT自增约束条件。

  • MySQL中整数型数据类型存储空间图如下:

    整数型数据类型存储需求.png

  • 不同整数类型的取值范围图如下:

    不同整数类型的取值范围.png

  • 在创建数据表时,指定INT(11)为字段id的数据类型,其中数字11表示的是该数据类型指定的显示宽度,指定能够显示的数值中数字的个数。但显示宽度和数据类型的取值范围是无关的。显示宽度只是指明MySQL最大可能显示的数字个数,数值的位数小于指定的宽度时会由空格填充,如果插入了大于显示宽度的值,只要该值不超过该类型整数的取值范围,数值依然可以插入,而且能够显示出来。当不指定显示宽度时,系统自动指定宽度,该宽度能够保证显示每一种数据类型可以取到取值范围内的所有值。

  • 提示:显示宽度只用于显示,并不能限制取值范围占用空间。例如,INT(3)会占用4字节的存储空间,并且允许的最大值也不会是999,而是INT整形所允许的最大值。

浮点数类型和定点数类型

  • MySQL中使用浮点数定点数来表示小数。浮点类型有两种:单精度浮点类型FLOAT)和双精度浮点类型DOUBLE)。定点类型只有一种:DECIMAL。浮点类型和定点类型都可以用(M,N)来表示,其中M称为精度,表示总共的位数;N称为标度,是表示小数的位数。

    小数数据类型存储需求.png

    DECIMAL类型不同于FLOATDOUBLEDECIMAL实际是以串存放的,DECIMAL可能的最大取值范围与DOUBLE一样,但是其有效的取值范围由M和D的值决定。

  • FLOAT类型的取值范围如下:

    • 有符号的取值范围:-3.402823466E+38 ~ -1.175494351E-38
    • 无符号的取值范围:0和1.175494351E-38 ~ 3.402823466E+38
  • DOUBLE类型的取值范围如下:

    • 有符号的取值范围:-1.7976931348623157E+308 ~ -2.2250738585072014E-308
    • 无符号的取值范围:0和2.2250738585072014E-308 ~ 1.7976931348623157E+308
  • 精度问题:

    • FLOATDOUBLE在不指定精度时,默认会采用实际的精度(由计算机硬件和操作系统决定),DECIMAL不指定精度时默认为 (10,0) 。浮点数相对于定点数的优点在长度一定的情况下能够表示更大的数据范围,缺点是会引起精度问题。
    • 无论是定点还是浮点类型,如果用户指定的精度超过精度范围,就会四舍五入地进行处理。FLOATDOUBLE类型在进行四舍五入时不会给出警告,但是DECIMAL类型会抛出警告。
  • 提示:在MySQL中,定点数以字符串形式存储,在对精度要求比较高的时候(如货币、科学数据等)使用DECIMAL的类型比较好。另外,两个浮点数进行减法和比较运算时也容易出问题,所以在使用浮点型时需要注意,并尽量避免作浮点数比较。

日期与时间类型

  • MySQL中有多种表示日期的数据类型,主要有DATETIMEDATETIMESTAMPTIMEYEAR。例如,当只记录年信息的时候,可以只使用YEAR类型,而没有必要使用DATE。每一个类型都有合法的取值范围,当指定确实不合法的值时系统将“”值插入到数据库中。

  • 日期与时间数据类型存储需求图如下:

    日期与时间数据类型存储需求.png

  • YEAR

    YEAR类型是一个单字节类型,用于表示年,在存储时只需要1字节。可以使用各种格式指定YEAR值,如下所示:

    1. 4位字符串或者4位数字格式表示的YEAR,范围为 ‘1901’ ~ ‘2155’ 。输入格式为 ‘YYYY’ 或者 YYYY
    2. 2位字符串格式表示的YEAR,范围为 ‘00’‘99’‘00’~‘69’‘70’~‘99’ 范围的值分别被转换为 2000 ~ 20691970 ~ 1999 范围的 YEAR 值。 ‘0’‘00’ 的作用相同。
    3. 2位数字表示的YEAR,范围为1~991~6970~99范围的值分别被转换为2001~20691970~1999范围的YEAR值。注意:在这里0值将被转换为0000,而不是2000。
    4. 插入非法数据系统将会抛出错误
  • TIME

    TIME类型用在只需要时间信息的值,在存储时需要3个字节。格式为 ‘HH:MM:SS’HH表示小时;MM表示分钟;SS表示秒。TIME类型的取值范围为 -838:59:59~838:59:59,小时部分会如此大的原因是TIME类型不仅可以用于表示一天的时间(必须小于24小时),还可能是某个时间过去的时间两个事件之间的时间间隔(可以大于24小时,或者甚至为)。可以使用各种格式指定TIME值,如下所示:

    1. ‘D HH:MM:SS’格式的字符串。还可以使用下面任何一种非严格的语法:‘HH:MM:SS’‘HH:MM’‘D HH:MM’‘D HH’‘SS’。这里的D表示,可以取0~34之间的值。在插入数据库时,D被转换为小时保存,格式为 “D*24+HH”

    2. ‘HHMMSS’格式的、没有间隔符的字符串或者HHMMSS格式的数值,假定是有意义的时间。例如 ‘101112’ 被理解为 ‘10:11:12’ ,但 ‘109712’ 是不合法的(它有一个没有意义的分钟部分),插入时会抛出错误。

    3. 冒号问题:为TIME列分配简写值应注意:如果没有冒号,MySQL解释值时,假定最右边的值表示秒。(MySQL解释TIME值为过去的时间而不是当天的时间)。例如,‘1112’1112表示时间时,MySQL将它们解释为00:11:12(11分12秒)。同样‘12’12被解释为00:00:12相反,TIME值中如果使用冒号则肯定被看作当天的时间。也就是说,‘11:12’表示11:12:00,而不是00:11:12

    4. 插入系统当前时间,SQL语句如下:

      insert into <表名> values(CURRENT_TIME),(NOW());
      
  • DATE类型

    DATE类型用在仅需要日期值时,没有时间部分,在存储时需要3个字节。日期格式为 ‘YYYY-MM-DD’。其中YYYY表示年,MM表示月,DD表示日。在给DATE类型的字段赋值时,可以使用字符串类型或者数字类型的数据插入,只要符合DATE的日期格式即可,如下:

    1. ‘YYYY-MM-DD’ 或者 ‘YYYYMMDD’ 字符串格式表示的日期,取值范围为 ‘1000-01-01’~‘9999-12-3’
    2. ‘YY-MM-DD’ 或者 ‘YYMMDD’ 字符串格式表示的日期,在这里表示YY表示两位的年值。包含两位年值的日期会令人模糊,因为不知道世纪。MySQL使用以下规则解释两位年值:‘00~69’ 范围的年值转换为 ‘2000~2069’‘70~99’ 范围的年值转换为 ‘1970~1999’
    3. YYMMDD数字格式表示的日期,与前面类似,00~69范围的年值转换为2000~206970~99范围的年值转换为1970~1999
    4. 使用CURRENT_DATE或者NOW( ),插入当前系统日期CURRENT_DATE只返回当前日期值,不包含时间部分;NOW( )函数返回日期和时间值,在保存到数据库时,只保留了其日期部分。
    5. MySQL允许 ”不严格“ 语法:任何标点符号都可以用作日期部分之间的分隔符。例如 ‘98-11-31’‘98.11.31’‘98/11/31’‘98@11@31’ 是等价的,这些值也可以正确地插入到数据库。
  • DATETIME

    DATETIME类型用在需要同时包含日期和时间信息的值,在存储时需要8字节,日期格式为 ‘YYYY-MM-DD HH:MM:SS‘ 。其中,YYYY表示年,MM表示月,DD表示日,HH表示小时,MM表示分钟,SS表示秒。在给DATETIME类型的字段赋值时,可以使用字符串类型或者数字类型插入,只要符合DATETIME的日期格式即可。

    1. ‘YYYY-MM-DD HH:MM:SS’ 或者 ‘YYYYMMDDHHMMSS’ 字符串格式表示的值,取值范围为 ‘1000-01-01 00:00:00’~'9999-12-3 23:59:59'
    2. ‘YY-MM-DD HH:MM:SS’ 或者 ‘YYMMDDHHMMSS’ 字符串格式表示的日期,在这里YY表示两位的年值,与前面的相同,‘00~69’ 范围的年值转换为 ‘2000~2069’‘70~99‘ 范围的年值转换为 ’1970~1999‘
    3. YYYYMMDDHHMMSS或者YYMMDDHHMMSS数字格式表示的日期和时间。
    4. NOW( )函数返回当前系统的日期和时间值,格式为 ’YYYY-MM-DD HH:MM:SS‘
    5. MySQL允许 “不严格” 语法:任何标点符号都可以用作日期部分或时间部分之间的间隔符。例如,’98-12-31 11:30:45‘'98.12.31 11+30+45'’98/12/31 11@30@45‘ 是等价的,这些值都可以正确地插入数据库。
  • TIMESTAMP

    TIMESTAMP的显示格式与DATETIME相同,显示宽度固定在19个字符,日期格式为YYYY-MM-DD HH:MM:SS,在存储时需要4字节,但是TIMESTAMP列的取值范围小于DATETIME的取值范围,为 ’1970-01-01 00:00:01‘ UTC ~ '2038-01-19 03:14:07' UTC,其中UTC(Coordinated Universal Time)为世界标准时间,因此在插入数据时,要保证在取值范围内。

    1. TIMESTAMPDATETIME除了存储字节和支持的范围不同外,还有一个最大的区别就是:DATETIME在存储日期数据时,按实际输入的格式存储,即输入什么就存储什么,与时区无关;而TIMESTAMP值的存储是以UTC(世界标准时间)格式保存的,存储时与当前时区进行转换,检索时再转换回当前时区。即查询时,根据当前时区的不同,显示的时间值是不同的。

    2. 设置系统时区的语句格式如下:

      set time_zone='+(-)10:00'
      

      其中 '+' 号表示东时区'-' 号表示西时区;+10:00表示东10区。

    3. 为一个DATETIMETIMESTAMP对象分配一个DATE值,结果值的时间部分被设置为 ’00:00:00‘ ,因为DATE值未包含时间信息。为一个DATE对象分配一个DATETIMETIMESTAMP值,结果值的时间部分被删除,因为DATE值未包含时间信息。

文本字符串类型

字符串类型用来存储字符串数据,除了可以存储字符串数据之外,还可以存储其他数据,比如图片和声音的二进制数据。MySQL支持两类字符型数据:文本字符串二进制字符串。文本字符串可以进行区分或者不区分大小写串比较,还可以进行模式匹配查找。MySQL中的文本字符串类型是指CHARVARCHARTEXTENUMSET。MySQL中的文本字符串数据类型列举如下:

文本字符串数据类型存储需求.png

VARCHARTEXT类型与下一小节将要讲到的BLOB一样是变长类型,对于其存储需求取决于列值的实际长度(在前面的表格中用L表示),而不是取决于类型的最大可能尺寸

  • CHAR和VARCHAR类型

    • CHAR(M)固定长度字符串,在定义时指定字符串列长。当保存时在右侧填充空格以达到指定的长度。M表示列长度,M的范围是0~255个字符。例如,CHAR(4)定义了一个固定长度的字符串列,其包含的字符个数最大为4。当检索到CHAR值时,尾部的空格(包括人为添加的空格)将被删除。
    • VARCHAR(M)长度可变的字符串,M表示最大列长度。M的范围是0~65535。VARCHAR的最大实际长度由最长的行的大小和使用的字符集确定,而其实际占用的空间为字符串的实际长度加1。例如,VARCHAR(50)定义了一个最大长度为50的字符串,如果插入的字符串只有10个字符,则实际存储的字符串为10个字符和一个字符串结束字符在值保存和检索时VARCHAR尾部的空格仍保留。
    • CHAR(4)VARCHAR(4) 存储区别如下所示:

    CHAR4与VARCHAR4存储区别.png

    对比结果可以看到, CHAR(4) 定义了固定长度为4的列,不管存入的数据长度为多少,所占用的空间均为4字节。 VARCHAR(4) 定义的列所占的字节数为实际长度加1。

    当查询时, CHAR(4)VARCHAR(4) 的值并不一定相同。

    • 只有在MySQL使用 “不严格” 模式时,字符串才会被截断插入;如果MySQL运行在 “严格” 模式,超过列长度的值不会被保存,并且会出现错误信息:“ERROR 1406(22001):Data too long for column”,即字符串长度超过指定长度,无法插入。
  • TEXT类型

    TEXT列保存非二进制字符串,如文章内容、评论等。当保存或查询TEXT列的值时,不删除尾部空格。TEXT类型分为4种:TINYTEXTTEXTMEDIUMTEXTLONGTEXT。不同的TEXT类型的存储空间和数据长度不同。

    1. TINYTEXT最大长度为 255(2^8 - 1 ) 字符的TEXT列。
    2. TEXT最大长度为 65535( 2^16 - 1 ) 字符的TEXT列。
    3. MEDIUMTEXT最大长度为 16777215( 2^24 - 1 ) 字符的TEXT列。
    4. LONGTEXT最大长度为 4294967295( 2^32 - 1 ) 字符的TEXT列。
  • ENUM类型

    • ENUM是一个字符串对象,其值为表创建时在列规定中枚举的一列值。语法格式如下:

      字段名 ENUM('值1','值2',...'值n')
      
    • 字段名指将要定义的字段,值n指枚举列表中的第n个值ENUM类型的字段在取值时,只能在指定的枚举列表中取,而且一次只能取一个,如果创建的成员中有空格时,其尾部的空格将自动被删除ENUM值在内部用整数表示,每个枚举值均有一个索引值;列表值所允许的成员值从1开始编号,MySQL存储的就是这个索引编号。枚举最多可以有65535个元素。

      例如,定义ENUM类型的列( 'first' , 'second' , 'third' ),该列可以取的值的索引如下表所示:

    ENUM类型的取值范围.png

    • ENUM值依照列索引顺序排列,并且空字符串排在非空字符串前,NULL值排在其他所有的枚举值前。

    • 查看列成员索引值的SQL语句如下:

      SELECT enm,enm+0 FROM 表名;
      
    • ENUM列总有一个默认值。如果将ENUM列声明为NULLNULL值则为该列的一个有效值,并且默认值NULL。若ENUM列被声明为NOT NULL,则其默认值为允许的值的列表的第一个元素

    • 当插入的字符串值不在ENUM列表中,会对数据进行阻止插入操作。

    • 空字符串的索引值为0。在插入空字符串之前,SQL模式不得为TRADITIONALSTRICT_TRANS_TABLES(MySQL5.7默认模式 )或STRICT_ALL_TABLES。这时可以通过SQL语句改变SQL模式,语句如下:

      set SESSION sql_mode = '';
      
  • SET类型

    • SET是一个字符串对象,可以有零或多个值SET列最多可以有64个成员,其值为表创建时规定的一列值。指定包括多个SET成员的SET列值时,各成员之间用逗号,)间隔开。语法格式如下:

      字段名 SET('值1','值2',...'值n')
      
    • ENUM类型相同SET值在内部用整数表示,列表中每一个值都有一个索引编号当创建表时,SET成员值的尾部空格将自动被删除。ENUM类型不同的是,ENUM类型的字段只能从定义的列值中选择一个值插入,而SET类型的列可从定义的列值中选择多个字符的联合。

    • 如果插入SET字段中列值有重复,那么MySQL会自动删除重复的值;插入SET字段的值的顺序不重要,MySQL会在存入数据库时按照定义的顺序显示;如果插入了不正确的值,默认情况下,MySQL将忽视这些值并给出警告。

二进制字符串类型

MySQL中的二进制数据类型BITBINARYVARBINARYTINYBLOBBLOBMEDIUMBLOBLONGBLOB。MySQL中二进制字符串类型列举如下:

二进制字符串类型存储需求.png

  • BIT类型

    • BIT类型是位字段类型。M表示每个值的位数,范围为1~64如果M被忽略,默认为1。如果为BIT(M)列分配的值的长度小于M位,在值的左边用0填充BIT数据类型用来保存位字段值,且其值不可以超过该列允许范围的值。例如,大于二进制1111的数据是不能插入BIT(4)类型的字段中的。

    • 以不同进制显示BIT类型的方法,SQL语句如下:

      1. 十进制

        select b+0 from 表名;
        
      2. 二进制

        select BIN(b+0) from 表名;
        
      3. 八进制

        select OCT(b+0) from 表名;
        
      4. 十六进制

        select HEX(b+0) from 表名;
        

      b+0表示将字节字符串转换为对应的十进制数值BIN( )函数将字节字符串转换为二进制OCT( )函数将字节字符串转换为八进制,而HEX( )函数则转换为十六进制

    • 数据插入问题

      1. 当插入字符BIT类型时,系统默认将该字符对应的ASCII值转换为对应的字节字符串进行存储,但该字节字符串不可超过该列所允许的范围,否则插入失败并返回错误信息。
      2. 当插入数值(二、八、十、十六进制等)到BIT类型时,系统将默认将数值转换为对应的字节字符串存储。同理,该字节字符串不可超过该列所允许的范围。
    • 检索问题

      当直接检索BIT类型时,系统将存储的字节字符串转换为ASCII值所对应的控制字符

  • BINARY和VARBINARY类型

    • BINARY和VARBINARY类似于CHARVARCHAR,不同的是它们包含二进制字节字符串。其使用的语法格式如下:

      列名称 BINARY(M)或者VARBINARY(M)
      
    • BINARY类型的长度是固定的, 指定长度之后,不足最大长度的,将在右边填充 ‘ \0 ’,以达到指定长度。 例如,指定列数据类型为BINARY(3),当插入‘a‘ 时,存储的内容实际为“a\0\0”,当插入“ab”时,实际存储的内容为“ab\0”,不管存储的内容是否达到指定的长度,其存储空间均为指定的值M。

    • VARBINARY类型的长度是可变的,指定好长度之后,其长度可以在0到最大值之间。例如,指定列数据类型为VARBINARY(20),如果插入值的长度只有10,那么实际占用的空间为字符串的实际长度加1。

    • CHARVARCHARBINARYVARBINARY类型的区别:

      1. 大小比较时:

        charvarchar的字符比较中,是忽略大小写最后的空格的;而binaryvarbinary的字节比较中,所有的信息都不会被忽略。

      2. 存储和取出时对尾部空格的处理:

        • char(N) 用来存储非二进制字符串,插入时,对于少于N个字符的会自动在尾部加空格,查询时,尾部的空格就会被丢弃掉;
        • vachar(N) 用来存储非二进制字符串,插入时,对于少于N个字符的不填补空格,查询时,尾部的空格不会被丢弃掉;
        • binary(N) 存储二进制字符串,插入时,少于N个字节的会自动在尾部加0x00,查询时,所有的字节都保留,返回定义长度的字节长度,在比较的时候,所有的字节都是有效的,并且0x00<space (space对应的是0x20);
        • varbinary(N) 在插入不会去填补0x00字节,查询的时候也不会丢弃任何字节,在比较的时候,所有的字节都是有效的,并且0x00<space (space对应的是0x20)。
  • BLOB类型

    • BLOB是一个二进制的对象,用来存储可变数量的数据。BLOB类型分为4种:TINYBLOBBLOBMEDIUMBLOBLONGBLOB。它们可容纳值的最大长度不同,其存储范围如下表所示:

      BLOB类型的存储范围.png

    • BLOB列存储的是二进制字符串字节字符串);TEXT列存储的是非二进制字符串字符字符串)。BLOB列没有字符集,并且排序和比较基于列值字节的数值TEXT列有一个字符集,并且根据字符集对值进行排序和比较。

如何选择数据类型

  • 浮点数与定点数

    DECIMAL在MySQL中是以字符串存储的,用于定义货币等对精确度要求较高的数据。在数据迁移中,float(M,D)非标准SQL定义,数据库迁移可能会出现问题,最好不要这样使用。另外,两个浮点数进行减法和比较运算时也容易出现问题,因此在进行计算的时候,一定要小心。如果进行数值比较,最好使用DECIMAL类型。

  • 日期与时间类型

    TIMESTAMP有一个DATETIME不具备的属性。默认情况下,当插入一条记录并没有指定TIMESTAMP这个列值时,MySQL会把TIMESTAMP列设为当前的时间。因此当需要插入记录的同时插入当前时间时,使用TIMESTAMP是方便的。另外,TIMESTAMP在空间上比DATETIME更有效。

  • CHARVARCHAR之间的特点与选择

    CHAR是固定长度,所以它的处理速度比VARCHAR的速度要快,缺点是浪费存储空间。所以,对存储不大但在速度上有要求的可以使用CHAR类型,反之可以使用VARCHAR类型来实现。

    存储引擎对于选择CHARVARCHAR的影响:

    • 对于MyISAM存储引擎:最好使用固定长度的数据列代替可变长度的数据列。这样可以使整个表静态化,从而使数据检索更快,用空间换时间。
    • 对于InnoDB存储引擎:使用可变长度的数据列,因为InnoDB数据表的存储格式不分固定长度和可变长度,因此使用CHAR不一定比使用VARCHAR更好,但由于VARCHAR是按照实际的长度存储,比较节省空间,因此对磁盘I/O数据存储总量比较好。
  • ENUM和SET

    ENUMSET的值是以字符串形式出现的,但在MySQL内部是以数值的形式存储的。

  • BLOB和TEXT

    BLOB二进制字符串TEXT非二进制字符串,两者均可存放大容量的信息。BLOB主要存储图片、音频信息等,而TEXT只能存储纯文本文件

常见运算符介绍

运算符概述

运算符是告诉MySQL执行特定算术或逻辑操作的符号。MySQL的内容运算符很丰富,主要有四大类,分别是算术运算符比较运算符逻辑运算符位运算符

  1. 算术运算符

    算术运算符用于各类数值运算,包括加(+)减(-)乘(*)除(/)求余(或称模运算,%)

  2. 比较运算符

    比较运算符用于比较运算,包括大于(>)小于(<)等于(=)大于等于(>=)小于等于(<=)不等于(!=),以及INBETWEEN ANDIS NULLGREATESTLEASTLIKEREGEXP等。

  3. 逻辑运算符

    逻辑运算符的求值所得结果均为1(true)0(false),这类运算符有逻辑非(NOT或者!)逻辑与(AND或者&&)逻辑或(OR或者||)逻辑异或(XOR)

  4. 位运算符

    位运算符参与运算的操作数按二进制位进行运算,包括位与(&)位或(|)位非(~)位异或(^)左移(<<)右移(>>) 6种。

算术运算符

  • 算术运算符是SQL中最基本的运算符,MySQL中的算术运算符如下表所示。

    运算符作用
    +加法运算
    -减法运算
    *乘法运算
    /除法运算,返回商
    %求余运算,返回余数
  • 在数学运算中,除数为0的除法是没有意义的,因此在除法运算中的除数不能为0,如果被0除,则返回结果为NULL。

比较运算符

  • 一个比较运算符的结果总是10或者NULL。比较运算符经常在SELECT的查询条件子句中使用,用来查询满足特定条件的记录。MySQL中的比较运算符如下表所示。

    运算符作用
    =等于
    <=>安全等于
    <>(!=)不等于
    <=小于等于
    >=大于等于
    >大于
    IS NULL判断一个值是否为NULL
    IS NOT NULL判断一个值是否不为NULL
    LEAST在有两个或多个参数时,返回最小值
    GREATEST在有两个或多个参数时,返回最大值
    BETWEEN AND判断一个值是否落在两个值之间
    ISNULL与IS NULL作用相同
    IN判断一个值是IN列表中的任意一个值
    NOT IN判断一个值不是IN列表中的任意一个值
    LIKE通配符匹配
    REGEXP正则表达式匹配
  1. 等于运算符(=)

    等号(=)用来判断数字字符串表达式是否相等。如果相等,返回值为1,否则返回值为0。

    数值比较时有如下规则:

    (1)若有一个或两个参数为NULL,则比较运算的结果为NULL

    (2)若同一个比较运算中的两个参数都是字符串,则按照字符串进行比较。

    (3)若两个参数均为整数,则按照整数进行比较。

    (4)若一个字符串数字进行相等判断,则MySQL可以自动将字符串转换为数字。

  2. 安全等于运算符(<=>)

    这个操作符和 = 操作符执行相同的比较操作,不过 <=> 可以用来判断NULL值。在两个操作数均为NULL时,其返回值为1而不为NULL;而当一个操作数为NULL时,其返回值为0而不为NULL

  3. 不等于运算符(<>或者!=)

    '<>' 或者 ‘!=’ 用于判断数字字符串表达式不相等的判断。如果不相等,就返回1,否则返回0。这两个运算符不能用于判断空值NULL

  4. 小于等于运算符(<=)

    '<=' 用来判断左边的操作数是否小于或者等于右边的操作数。如果小于或者等于,返回值为1,否则返回值为0。'<=' 不能用于判断空值NULL

  5. 小于运算符(<)

    '<' 用来判断左边的操作数是否小于右边的操作数。如果小于,返回值为1,否则返回值为0。'<' 不能用于判断空值NULL

  6. 大于等于运算符(>=)

    '>=' 用来判断左边的操作数是否大于或者等于右边的操作数。如果大于或者等于,返回值为1,否则返回值为0。'>=' 不能用于判断空值NULL

  7. 大于运算符(>)

    '>' 用来判断左边的操作数是否大于右边的操作数。如果大于,返回值为1,否则返回值为0。'>' 不能用于判断空值NULL

  8. IS NULL( ISNULL(参数) )和 IS NOT NULL运算符

    IS NULLISNULL( ) 检验一个值是否为NULL,如果为NULL,返回值为1,否则返回值为0。IS NOT NULL检验一个值是否为非NULL,如果为非NULL,返回值为1,否则返回值为0。

  9. BETWEEN AND运算符

    语法格式为:

    expr BETWEEN min AND max
    

    假如expr大于或等于min且小于或等于max,则BETWEEN的返回值为1,否则返回值为0。

  10. LEAST运算符

    语法格式为:

    LEAST(值1,值2,...,值n)
    

    其中,值n表示参数列表中有n个值。在有两个或多个参数的情况下,返回最小值。假如任意一个自变量为NULL,则LEAST( )的返回值为NULL

  11. GREATEST(value1,value2,...)

    语法格式为:

    GREATEST(值1,值2,...,值n)
    

    其中,值n表示参数列表中有n个值。在有两个或多个参数的情况下,返回最小值。假如任意一个自变量为NULL,则GREATEST( )的返回值为NULL

  12. IN、NOT IN运算符

    IN运算符用来判断操作数是否为IN列表中的其中一个值,如果是,返回1,否则返回0。

    NOT IN运算符用来判断操作数是否为IN列表中的其中一个值,如果不是,返回1,否则返回0。

    在左侧表达式为NULL的情况下,或是表中找不到匹配项并且表中一个表达式为NULL的情况下,IN的返回值为NULL

  13. LIKE

    LIKE运算符用来匹配字符串,语法格式为:

    expr LIKE 匹配条件
    

    如果expr满足匹配条件,就返回1(true);如果不匹配,就返回0(false);若expr或匹配条件中任何一个为NULL,则结果为NULL

    LIKE运算符在进行匹配时,可以使用下面两种通配符

    (1)'%' ,匹配任何数目的字符,甚至包括零字符。

    (2)'_' ,只能匹配一个字符。

  14. REGEXP

    REGEXP运算符用来匹配字符串,语法格式为:

    expr REGEXP 匹配条件
    

    如果expr满足匹配条件,就返回1;如果不满足,则返回0;若expr或匹配条件任意一个为NULL,则结果为NULL

    REGEXP运算符在进行匹配时,常用的有下面几种通配符

    (1)'^' 匹配以该字符后面的字符开头的字符串。

    (2)'$' 匹配以该字符后面的字符结尾的字符串。

    (3)'.' 匹配任何一个单字符

    (4)"[...]" 匹配在方括号内的任何字符。例如,"[abc]" 匹配 "a","b"或"c"。为了命名字符的范围,使用一个 '-' 。例如,"[a-z]" 匹配任何字母,"[0-9]" 匹配任何数字。

    (5)'*' 匹配零个或多个在它前面的字符。例如,"x * " 匹配任何数量的 'x' 字符,"[0-9] * " 匹配任何数量的数 字,而 "*" 匹配任何数量的任何字符。

逻辑运算符

  • 在SQL中,所有逻辑运算符的求值所得结果均为truefalseNULL。在MySQL中,它们表现为1(true)0(false)NULL。其大多数都与不同的数据库SQL通用,MySQL中的逻辑运算符如下表所示。

    运算符作用
    NOT或者!逻辑非
    AND或者&&逻辑与
    OR或者||逻辑或
    XOR逻辑异或
  1. NOT或者!

    逻辑非运算NOT或者 表示当操作数为0时,所得值为1;当操作数为非零值时,所得值为0;当操作数为NULL时,所得值为NULL。(”NOT“”!“ 的返回值相同,但优先级别不同。其中 ”NOT“ 的优先级低于”+“,而 ”!“ 的优先级要高于”+“运算。)

    注意:在使用运算符运算时,一定要注意不同运算符优先级不同,如果不能确定计算顺序,最好使用括号,以保证运算结果正确。

  2. AND或者&&

    逻辑与运算符AND或者 && 表示当所有操作数均为非零值并且不为NULL时,计算所得结果为1;当一个或多个操作数为0时,所得结果为0;其余情况返回值为NULL

    注意:"AND"运算符可以有多个操作数,但要注意:多个操作数运算时,AND两边一定要使用空格隔开,不然会影响结果的正确性。

  3. OR或者||

    逻辑或运算OR或者||表示当两个操作数均为非NULL值且任意一个操作数为非零值时,则结果为1,否则结果为0;当有一个操作数为NULL,且另一个操作数为非零值时,则结果为1,否则结果为NULL;当两个操作数为NULL时,则所得结果为NULL

  4. XOR

    逻辑异或运算符XOR。当任意一个操作数为NULL时,返回值为NULL;对于非NULL的操作数,如果两个操作数都是非0值或者都是0值,则返回结果为0;如果一个为0值,另一个为非0值,返回结果为1

    提示:a XOR b 的计算等同于 a AND (NOT b) 或者 (NOT a) AND b

位运算符

  • 位运算符用来对二进制字节中的位进行测试移位或者测试处理。MySQL中提供的位运算符有按位或(|)按位与(&)按位异或(^)按位左移(<<)按位右移(>>)按位取反(~),MySQL中的位运算符如下表所示。

    运算符作用
    |位或
    &位与
    ^位异或
    <<位左移
    >>位右移
    ~位取反,反转所有位
  1. 位或运算符(|)

    位或运算的实质是将参与运算的几个数据按对于的二进制数逐位进行逻辑或运算。对应的二进制位有一个或两个为1则该位的运算结果为1,否则为0

  2. 位与运算符(&)

    位与运算的实质是将参与运算的几个数据按对于的二进制数逐位进行逻辑与运算。对应的二进制位都为1,则该位的运算结果为1,否则为0

  3. 位异或运算符(^)

    位异或运算的实质是将参与运算的几个数据按对于的二进制数逐位进行逻辑异或运算。对应的二进制位不同时,对应位的结果才为1,否则为0

  4. 位左移运算符(<<)

    位左移运算符<<使指定的二进制值的所有位都左移指定的位数。左移指定的位数之后,左边高位的数值将被移出并丢弃右边低位空出的位置用0补齐。语法格式为:

    expr<<n
    

    这里n指定值expr要移位的位数

  5. 位右移运算符(>>)

    位右移运算符>>使指定的二进制值的所有位都右移指定的位数。右移指定的位数之后,右边低位的数值将被移出并丢弃左边高位空出的位置用0补齐。语法格式为:

    expr>>n
    

    这里n指定值expr要移位的位数

  6. 位取反运算符(~)

    位取反运算的实质是将参与运算的数据,按对应的二进制数逐位反转,即1取反之后变00取反后变为1

  • MySQL经过位运算之后的数值是一个64位的无符号整数。可以使用BIN( )函数查看数值取反之后的结果。

运算符的优先级

  • 运算符的优先级决定了不同的运算符在表达式中计算的先后顺序。下表列出了MySQL中的各类运算符及其优先级。

    优先级运算符
    最低=(赋值运算),:=
    ||,OR
    XOR
    &&,AND
    NOT
    BETWEEN,CASE,WHEN,THEN,ELSE
    =(比较运算),<=>,>=,>,<=,<,<>,!=,IS,LIKE,REGEXP,IN
    |
    &
    <<
    >>
    -,+
    *,/(DIV),%(MOD)
    ^
    -(负号),~(位反转)
    最高
  • 一般情况下,级别高的运算符先进行计算,如果级别相同,MySQL按表达式的顺序从左到右依次计算。当然,在无法确定优先级的情况下,可以使用圆括号()来改变优先级,并且这样会使计算过程更加清晰。

注意事项

  • 在MySQL中如何使用特殊字符?

    诸如单引号(')、双引号('')、反斜线(\)等符号,这些符号在MySQL中不能直接输入使用,否则会产生意科之外的结果。在MySQL中,这些特殊字符称为转义字符,在输入时需要以反斜线符号('\')开头, 所以在使用单引号和双引号时应分别输入 (\')或者(\"),输入反斜线时应该输入(\\),其他特殊字符还有回车符(\r)、换行符(\n)、制表符(\tab)、退格符(\b)等。在向数据库中插入这些特殊字符时,一定要进行转义处理。

  • 在MySQL中可以存储文件吗?
    MySQL中的BLOBTEXT字段类型可以存储数据量较大的文件,可以使用这些数据类型存储图像声音或者大容量的文本内容,例如网页或者文档。虽然使用BLOB或者TEXT可以存储大容量的数据,但是对这些字段处理会降低数据库的性能。如果并非必要,可以选择只储存文件的路径

  • 在MySQL中如何执行区分大小写的字符串比较?
    Windows平台下,MySQL是不区分大小的,因此字符串比较函数也不区分大小写,如果想执行区分大小写的比较,可以在字符串前面添加BINARY关键字。例如,默认情况下,'a' = 'A' 返回结果为1;如果使用BINARY关键字,BINARY 'a' = 'A' 结果为0,因为在区分大小写的情况下,'a' 与 'A' 并不相同。


标题:MySQL数据类型和运算符(四)
作者:wangdj
地址:https://www.dblog.cool/articles/2024/11/04/1730733541063.html