数据类型

　　MySQL提供了多种数据类型，主要包括：数值型、字符串类型、日期和时间类型JSON类型。

MySQL中的数值类型

MySQL中的日期和时间类型

• 如果要用来表示年月日，通常用DATE来表示；
• 如果要用来表示年月日时分秒，通常用DATETIME或者TIMESTAMP表示；
  • 相同点：可自动更新和初始化，默认显示格式相同YYYY-MM-dd HH:mm:ss
  • timestamp的时间范围是：'1970-01-01 00:00:01'UTC to'2038-01-19 03:14:07' UTC ,自动时区转化,实际存储毫秒数,4字节存储 
  • datetime的时间范围：'1000-01-01 00:00:00' to '9999-12-31 23:59:59' ,不支持时区，8字节存储
• 如果只用来表示时分秒，通常用TIME来表示；
• 如果只是表示年份， 可以用YEAR来表示，它比DATE占用更少的空间。
  • 默认的是4位格式，允许的值是1901~2155和0000。
  • 2位格式中，允许的值是70~69，表示从1970~2069年。
  • MySQL以YYYY格式显示YEAR值（从5.5.27开始，2位格式的year已经不被支持）。

　　注意：每种日期时间类型都有一个有效值范围，如果超出这个范围，在默认的SQLMode下，系统会进行错误提示，并将以零值来进行存储。

字符串类型

1、MySQL提供了多种对字符数据的存储类型，不同的版本可能有所差异，以5.7版本为例，MySQL包括了CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET等多种字符串类型，如下表所示：

2、CHAR和VARCHAR类型：

两者的主要区别在于存储方式不同：

CHAR列的长度固定为创建表时声明的长度，而VARCHAR列中的值为可变长字符串；

在检查的时候，CHAR列删除尾部的空格，而VARCHAR则保留这些空格

• 测试：创建测试表vc，并定义两个字段“v VARCHAR(4)”和“c CHAR(4)”；
• v列和c列中同时插入字符串“ab “；再给两个字段分别追加一个"+"字符串。

  create table vc(
          v varchar(4),
          c char(4)
  )Engine=Innodb default charset=utf8;
  insert into vc values("ab ","ab ");
  select length(v),LENGTH(c) from vc;
  select concat(v,"+"),concat(c,"+") from vc;

  　　　　

3、BINARY和VARBINARY类型：

BINARYT和VARBINARY类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不包含非二进制字符串。

4、ENUM类型：

ENUM中文名称叫枚举类型，它的值范围需要在创建表时通过枚举方式显示指定，对1~255个成员的枚举需要1个字节存储，对于255~65535个成员，需要2个字节来存储。最多允许65535个成员。

• 测试：定义gender字段为枚举类型，成员为”M“和”F“；

  create table t (gender enum('M','F'));

5、SET类型：

SET和ENUM类型非常类似，也是一个字符串对象，里面可以包含0~64个成员。根据成员的不同，存储上也有所不同。

1~8成员的集合，占1个字节

9~16成员的集合，占2个字节

17~24成员的集合，占3个字节

25~32成员的集合，占4个字节

33~64成员的集合，占8个字节

注意：

SET和ENUM除了存储以外，最主要的区别在于SET类型一次可以行取多个成员，而ENUM则只能选一个。

SET中重复值只取一次

超出范围的内值不允许插入操作

JSON类型

1、JSON类型比字符类型有如下优点：

JSON数据类型会自动校验数据是否为JSON格式，如果不是JSON格式数据，则会报错；

MySQL提供了一组操作JSON数据的内置函数，可以方便地提取各类数据，可以修改特定的键值；

优化的存储格式，存储在JSON列中的JSON数据被转换成内部的存储格式，允许快速读取；

简单地说，JSON实际就是Javascript的一个子集，支持的数据类型包括

NUMBER、STRING、BOLLEAN、NULL、ARRAY、OBJECT共6种，一个JSON中的元素可以是这6种类型元素的任意组合；

BOOLEAN：true/false

NULL：null

字符串和日期类型：用双引号引起来

ARRAY：括号引起来

OBJECT：KV，要用大括号引起来

注意：JSON，数据类型对于大小写是敏感的，常见的null、true、false必须是小写才合适。

2、必须要使用navicat 12.1.x 版本才能正常创建json 类型的字段

navicat 12.1.x 以下版本 类型手动写json，也可以创建表成功，但是查询结构显示不了，需要使用mysql自带客户端查询： select * from 表名称 where json_contains(字段名称,'包含的值'); ，

如： select * from test where json_contains(dd,'1');

算术运算符

1、+：运算符用于获得一个或多个值的和；

2、-：运算符用于从一个值中减去另一个值；

3、*：运算符使数字相乘，得到两个或多个值的乘积；

4、/：运算符用一个值除以另一个值得到商；DIV

5、%：运算符用一个值除以另外一个值得到余数；MOD

注意：在除法运算和模运算中，如果除数为0，将是非法除数，返回结果为NULL。

比较运算符

1、“=”运算符：用于比较运算符两侧的操作数是否相等，如果两侧操作数相等，则返回值为1，否则为0。注意NULL不能用于“=”比较。

2、“<>”运算符：和“=”相反，如果两侧操作数不等，则值为1，否则为0。NULL不能用于“<>”比较。

3、“<=>”运算符：和”=“类似，在操作数相等时值为1，不同之处在于即使操作的值为NULL也可以正确比较。

4、”<”运算符：当左侧操作数小于右侧操作数时，其返回值为1，否则其值为0。

5、“<=”运算符：当左侧操作数小于等于右侧操作数时，其返回值为1，否则返回值为0。

6、“>”运算符：当左侧操作数大于右侧操作数时，其返回值为1，否则返回值为0。

7、”>=”运算符：当左侧操作大于等于右侧操作数时，其返回值为1，否则返回值为0。

8、”BETWEEN“运算符的使用格式为“a BETWEEN min AND max”，当a大于等于min并且小于等于max，则返回值为1，否则返回0。

9、”IN“运算符使用的格式为”a IN(value1,value2...)“，当a的值存在于列表中时，则整个比较表达式返回的值为1，否则返回0。

10、”IS NULL“运算符的使用格式为”a IS NULL“，当a的值为NULL，则返回值为1，否则返回值为0。

11、”IS NOT NULL“运算符的使用格式为”a IS NOT NULL“，当a的值为不为NULL，则返回值为1，否则返回值为0。

12、”LIKE“运算符的使用格式为”a LIKE %123%“，当a中含有字符串”123“时，则返回值为1，否则返回值0。

13、”REGEXP“运算符的使用格式为”str REGEXP str_pat“，当字符串中含有str_pat相匹配的字符串，则返回值为1，否则返回0

LIKE 和 REGEXP之间的重要差别 LIKE 匹配整个列，如果被匹配的文本在列值中出现，LIKE 将不会找到它，相应的行也不会被返回（除非使用通配符）。而 REGEXP 在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP 将会找到它，相应的行将被返回，并且 REGEXP 能匹配整个列值（与 LIKE 相同的作用）。

逻辑运算符

1、“NOT” 或 “!” 表示逻辑非。

2、“AND” 或 “&&” 表示逻辑与运算。

3、“OR” 或 “||” 表示逻辑或运算。

4、“XOR” 表示逻辑异或。

位运算符

1、“&” 位与：对多个操作数的二进制位做逻辑与操作。

2、“|” 位或：对多个操作数的二进制位做逻辑或操作。

3、“^” 位异或：对操作数的二进制位做异或操作 。

4、“~” 位取反：对操作数的二进制位做NOT操作。

5、“>>” 位右移：对左操作数向右移动右操作数指定的位数。

6、“<<” 位左移：对左操作数向左移动右操作烽指定的位数。

运算符的优先级

:= 和 = 运算符在大部分场景下并无区别，但 := 更为全场景些。

= 只有在 set 和update时才是和 := 一样，赋值的作用，其它都是关系运算符 等于 的作用。

:= 不只在 set 和 update 时赋值的作用，在 select 也是赋值的作用。