MySQL学习笔记02

最新推荐文章于 2024-10-13 21:08:42 发布
最新推荐文章于 2024-10-13 21:08:42 发布
阅读量890 收藏 10
点赞数 14
文章标签： mysql 学习 笔记
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Zoey_ov/article/details/135869541
版权

三、MySQL支持的数据类型

MySQL提供了多种数据类型，主要包括数值型、字符串类型、日期和时间类型。不同版本的MySQL支持的数据类型可能会稍有不同，可以通过查询相应版本的帮助文件来获取具体信息。

这里介绍的是以MySQL5.7为例的MySQL中的各种数据类型。

1.数值类型

MySQL支持所有标准SQL中的数值类型，包括严格数值类型（INTEGER、SMALLINT、DECIMAL和NUMERIC），近似数值类型（FLOAT、REAL和DOUBLE PRECISION），在此基础上扩展增加了TINYINT、MEDIUNINT和BIGINT这三种长度不同的整型，还增加了BIT类型，用来存放位数据。

表3-1 MySQL中的数值类型
整数类型字节最小值最大值
TINYINT 1

有符号 -128

无符号 0

有符号 127 

无符号 255

SMALLINT 2

有符号 -32768

无符号 0

有符号 32737

无符号 65535

MEDIUMINT3

有符号 -8388608

无符号 0

有符号 8388607

无符号 1677215

INT、INTEGER4

有符号 -2147483648

无符号 0

有符号 2147483647

无符号 4294967295

BIGINT8

有符号 -9223372036854775808

无符号 0

有符号 9223372036854775807

无符号 18446744073709551615

浮点数类型字节最小值最大值
FLOAT4±1.17549435E-38±3.402823466E+38
DOUBLE8±2.2250738585072014E-308±1.7976931348623157E+308
定点数类型字节描述

DEC(M,D)

DECIMAL(M,D)

M+2最大取值范围与DOUBLE相同，给定DICIMAL的有效取值范围由M和D决定
位类型字节最小值最大值
BIT(M)1~8BIT(1)BIT(64)

超出类型范围的操作，会发生“Out of range”的错误提示。

1.1整型数据

对于整型数据，MySQL支持在类型名称后面的小括号内制定显示宽度。

例如，int(5)表示当数值宽度小于5位时在数字前面填满宽度，不指定显示宽度的话，默认为int(11)。一般配合zerofill使用，就是用“0”填充，在数字位数不够的空间用字符“0”填满。

示例：创建表t1，有两个字段id1和id2，制定数值宽度分别为int和int(5)。在id1和id2中都插入数值1，可以发现格式没有异常。修改id1和id2的字段类型，加入zerofill参数，可以发现在数值前面用字符“0”填充了剩余的宽度。

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| test1              |
| world              |
+--------------------+
7 rows in set (0.01 sec)

mysql> USE test1;
Database changed
mysql> CREATE TABLE t1 (id1 int,id2 int(5));
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> INSERT INTO t1 VALUES(1,1);
Query OK, 1 row affected (0.01 sec)

mysql> DESC t1;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id1   | int  | YES  |     | NULL    |       |
| id2   | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> SELECT * FROM t1;
+------+------+
| id1  | id2  |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 MODIFY id1 int ZEROFILL;
Query OK, 1 row affected, 1 warning (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> ALTER TABLE t1 MODIFY id2 int(5) ZEROFILL;
Query OK, 1 row affected, 2 warnings (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 2

mysql> SELECT * FROM t1;
+------------+-------+
| id1        | id2   |
+------------+-------+
| 0000000001 | 00001 |
+------------+-------+
1 row in set (0.00 sec)

如果插入的数值宽度大于宽度限制，不会对插入的数据有任何影响，还是按照类型的实际精度进行保存。 

mysql> INSERT INTO t1 VALUES(1,1111111);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+------------+---------+
| id1        | id2     |
+------------+---------+
| 0000000001 |   00001 |
| 0000000001 | 1111111 |
+------------+---------+
2 rows in set (0.00 sec)

所有整数类型都有一个可选属性UNSIGNED（无符号），需要在字段中保存非负数或者需要较大的上限值时，可以选用该属性。当一个列指定为zerofill时，MySQL自动为该列添加UNSIGNED属性。

整数类型还有一个属性：AUTO_INCREMENT，需要产生唯一标识符或顺序只时，可利用该属性，并且该属性只属于整数类型。AUTO_INCREMENT值一般从1开始，每行增加1。在插入NULL到一个AUTO_INCREMENT 列时，MySQL会插入一个比该列中当前最大值大1的值。一个表中最多只能有一个AUTO_INCREMENT 列，对于任何要使用该属性的列，应该定义为NOT NULL，并且定义为PREMARY KEY或定义为UNIQUE键。

下面三种方式都可定义AUTO_INCREMENT 列：

CREATE TABLE AI (ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY);
CREATE TABLE AI (ID INT AUTO_INCREMENT NOT NULL,PRIMARY KEY(ID));
CREATE TALBE AI (ID INT AUTO_INCREMENT NOT NULL,UNIQUE(ID));

1.2小数型数据

对于小数的表示，MySQL分为浮点数和定点数两种方式，浮点数包括float（单精度）和double（双精度），而定点数只有decimal一种表示。定点数在MySQL内部以字符串形式存放，比浮点数更精确，适合用来表示货币等精度高的数据。

浮点数和定点数都可以用类型名称后面加“（M,D）”的方式来表示，“（M,D）”表示一共显示M位数字（整数位+小数位），其中D位位于小数点后面，M和D又称为精度和标度。

例如float(7,4)的一个列可以显示为-999.9999，MySQL保存值时进行四舍五入，如果在float(7,4)列插入999.00009，近似结果为999.0001。

注：浮点数后面跟“（M,D）”的用法是非标准用法，如果要用于数据库的迁移，最好不用这么用。

float和double在不指定精度时，默认按照实际的精度（由实际的硬件和操作系统决定）来显示；有精度和标度，会自动将四舍五入后的结果插入，系统不会报错。而decimal不指定精度时，默认整数位为10，默认小数位为0；指定精度后，如果数据超过了精度和标度位，系统会报错。

1.3位类型数据

对于BIT（位）类型，用于存放位字段，BIT(M)可以用来存放多位二进制数，M范围为1~64，不写就默认为1位。

对于位字段，直接使用SELECT命令不会看到结果（实际测试时有结果，可能是因为MySQL版本更新了，采用的是MySQL8.0），可用bin()（显示为二进制格式）或者hex()（显示为16进制格式）函数进行读取。

mysql> CREATE TABLE t2 (id bit);
Query OK, 0 rows affected (0.03 sec)

mysql> DESC t2;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id    | bit(1) | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> INSERT INTO t2 VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t2;
+------------+
| id         |
+------------+
| 0x01       |
+------------+
1 row in set (0.00 sec)

mysql> SELECT bin(id),hex(id) FROM t2;
+---------+---------+
| bin(id) | hex(id) |
+---------+---------+
| 1       | 1       |
+---------+---------+
1 row in set (0.00 sec)

 数据插入bit类型时，首先转换为二进制，如果位数允许，成功插入；如果位数小于实际定义的位数，则插入失败。

 

 mysql> INSERT INTO t2 VALUES(2);
ERROR 1406 (22001): Data too long for column 'id' at row 1

2.日期时间类型

MySQL中有多种数据类型可以用于日期和时间的表示，不同的版本可能有差异。下面给出了MySQL5.7中所支持的日期和时间类型。

表3-2 MySQL中的日期和时间类型
日期和时间类型字节最小值最大值零值表示
DATE41000-01-019999-12-310000-00-00 
DATETIME81000-01-01 00:00:009999-12-31 23:59:590000-00-00 00:00:00
TIMESTAMP4197001010800012038年的某一个时刻00000000000000
TIME3-838:59:59838:59:5900:00:00
YEAR1190121550000

（1）表示年月日，通常用DATE来表示；

（2）表示年月日时分秒，通常用DATATIME或者TIMESTAMP来表示；

（3）只表示时分秒，通常用TIME来表示；

（4）只表示年份，可以用YEAR来表示，比DATE占用更少的空间。YEAR有2位或4位格式的年，默认是4位格式。在4位格式中，允许的值是1901~2155和0000。在2位格式中，允许的值是70~69，表示1970~2069年。MySQL以YYYY格式显示YEAR值（从MySQL5.5.27开始，2位格斯的year已经不被支持。） 

（5）每种日期时间类型都有一个有效范围值，超出这个范围，在默认的SQLMode下，系统会进行错误提示，并将以零值来存储。不同日期类型的零值表示见表3-2。

示例：在DATE,TIME和DATATIME三种类型字段中插入相同的日期值。

mysql> CREATE TABLE t (d date,t time,dt datetime);
Query OK, 0 rows affected (0.02 sec)

mysql> DESC t;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d     | date     | YES  |     | NULL    |       |
| t     | time     | YES  |     | NULL    |       |
| dt    | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> INSERT INTO t VALUES(now(),now(),now());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SELECT * FROM t;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2024-01-26 | 18:52:23 | 2024-01-26 18:52:23 |
+------------+----------+---------------------+
1 row in set (0.00 sec)

2.1TIMESTAMP类型的一些特性：

explicit_defaults_for_timestamp（5.6版本后引入）参数的值如果为OFF（MySQL8.0.36默认为ON），则系统会为TIMESTAMP类型的字段自动创建默认值CURRENT_TIMESTAMP(系统日期），并且设置了not full和on update CURRENT_TIMESTAMP属性。

mysql> set explicit_defaults_for_timestamp=OFF;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW variables like 'explicit%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF   |
+---------------------------------+-------+
1 row in set, 1 warning (0.00 sec)


mysql> CREATE TABLE t (id1 timestamp);
Query OK, 0 rows affected (0.02 sec)

mysql> DESC t;
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| Field | Type      | Null | Key | Default           | Extra                                         |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| id1   | timestamp | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
1 row in set (0.00 sec)

插入一个NULL值试试，t中自动插入了系统日期。

mysql> INSERT INTO t VALUES(null);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t;
+---------------------+
| id1                 |
+---------------------+
| 2024-01-26 19:13:13 |
+---------------------+
1 row in set (0.00 sec)

当explicit_defaults_for_timestamp参数值为OFF时，如果增加第二个TIMESTAMP类型，系统会报错。只能将参数值更改为ON之后再增加第二个TIMESTAMP类型，此时默认值为NULL。

mysql> SET explicit_defaults_for_timestamp=on;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE t ADD id3 timestamp;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t;
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| Field | Type      | Null | Key | Default           | Extra                                         |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| id1   | timestamp | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| id2   | time      | YES  |     | NULL              |                                               |
| id3   | timestamp | YES  |     | NULL              |                                               |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
3 rows in set (0.00 sec)

在MySQL5.6之前，可以修改id3的默认值为其他常量日期，但不能修改为current_timestamp，这是由于MySQL规定TIMESTAMP类型字段只能有一列的默认值为current_timestamp。

MySQL5.6版本之后，该限制去掉了，可以随意修改。

mysql> ALTER TABLE t MODIFY id3 timestamp default current_timestamp on update CURRENT_TIMESTAMP;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t;
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| Field | Type      | Null | Key | Default           | Extra                                         |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| id1   | timestamp | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| id2   | time      | YES  |     | NULL              |                                               |
| id3   | timestamp | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
3 rows in set (0.00 sec)

当explicit_defaults_for_timestamp参数值为ON时，默认值、not null 和on update CURRENT_TIMESTAMP属性都不会自动设置，需要手动操作。

TIMESTAMP字段还有一个重要特点，就是和时区相关。插入日期时，会先转换为本地时区后存放；从数据库里面取出时，也同样需要将日期转换为本地时区后显示。于是两个不同时区的用户可能看到的同一个日期时不一样的。

TIMESTAMP支持的时间范围较小，不适合存放比较久远的日期。TIMESTAMP和DATETIME都可以设置默认值和ON UPDATE CURRENT_TIMESTAMP属性，使得日期列可以随其他列的更新而自动更新为最新时间。

TIMESTAMP在MySQL5.6.6版本之后增加了控制参数explicit_defaults_for_timestamp，如果设置为on，则需要显示指定默认值和ON UPDATE CURRENT_TIMESTAMP属性；如果设置为off，则会自动设置默认值为CURRENT_TIMESTAMP（系统时间）和ON UPDATE CURRENT_TIMESTAMP属性，并且自动设置为not null。MySQL8.0.2之后此参数默认为on，之前版本默认为off。

当explicit_defaults_for_timestamp参数值为off时，表中的第一个TIMESTAMP列自动设置为系统时间；在一个TIMESTAMP列中插入NULL时，该列只将会自动设置为当前的日期和时间；在插入或更新一行但不明确给出TIMESTAMP列赋值时，也会自动设置该列的值为当前的日期和时间。当插入的值超出范围时，使用“0000-00-00 00:00:00”进行填补。

TIMESTAMP的插入和查询都受到当地时区的影响，更能反映出实际的日期；而DATATIME则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差。

TIMESTAMP的属性受MySQL版本和服务器SQLMode的影响很大，本文介绍的是以MySQL5.7为例的，不同版本的可以参考相应的MySQL帮助文档。

什么样的格式能够正确插入到对应的日期字段中？下面以DATETIME为例。

（1）YYYY-MM-DD HH:MM:SS或YY-MM-DD HH:MM:SS格式的字符串

允许不严格语法，即任何标点符号都可以作为日期部分或者时间部分之间的间隔符。

例如“98.12.31 11+30+45”

（2）YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的没有间隔符的字符串，并且字符串应当对于日期类型是有意义的。

（3）YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的数字，假定数字对于日期类型是有意义的。

（4）函数返回的结果，其值适合DATETIME、DATE或TIMESTAMP上下文，例如now()函数。

3.字符串类型

MySQL提供了多种对字符数据的存储类型，不同的版本可能有所差异。以MySQL5.7为例。

表3-3详细列出了这些字符串类型的比较。

表3-3 MySQL中的字符类型
字符串类型字节描述及存储需求
CHAR(M)MM为0~255之间的整数
VARCHAR(M)M为0~65535之间的整数，值的长度+1个字节
TINYBLOB允许长度0~255字节，值的长度+1个字节
BLOB允许长度0~65535字节，值的长度+2个字节
MEDIUMBLOB允许长度0~167772150字节，值的长度+3个字节
LONGBLOB允许长度0~4294967295字节，值的长度+4个字节
TINYTEXT允许长度0~255字节，值的长度+2个字节
TEXT允许长度0~65535字节，值的长度+2个字节
MEDIUMTEXT允许长度0~167772150字节，值的长度+3个字节
LONGTEXT允许长度0~4294967295字节，值的长度+4个字节
VARBINARY(M)

允许长度0~M个字节的变长字节字符串，值的长度+1个字节

BINARY(M)M允许长度0~M个字节的定长字节字符串

3.1CHAR和VARCHAR类型

CHAR和VARCHAR很类似，都用来保存MySQL中较短的字符串，两者的主要区别在于存储方式不同：CHAR列的长度固定为创建表时声明的长度；而VARCHAR列中的值为可变长字符串。在检索时，CHAR列删除了尾部的空格，而VARCHAR保留这些空格。

示例：给表vc中的VARCHAR(4)和CHAR(4)字段插入相同的字符串。

mysql> CREATE TABLE vc(v VARCHAR(4),c CHAR(4));
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO vc VALUES('ab  ','ab  ');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT lENGTH(v),LENGTH(c) FROM vc;
+-----------+-----------+
| lENGTH(v) | LENGTH(c) |
+-----------+-----------+
|         4 |         2 |
+-----------+-----------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT(v,'+'),CONCAT(c,'+') FROM vc;
+---------------+---------------+
| CONCAT(v,'+') | CONCAT(c,'+') |
+---------------+---------------+
| ab  +         | ab+           |
+---------------+---------------+
1 row in set (0.00 sec)

3.2BINARY和VARBINARY类型

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

示例：对表t中的binary字段c插入一个字符，研究这个字符是如何存储的。

mysql> CREATE TABLE t (c BINARY(3));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t SET c='a';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT *,hex(c),c='a',c='a\0',c='a\0\0' FROM t;
+------------+--------+-------+---------+-----------+
| c          | hex(c) | c='a' | c='a\0' | c='a\0\0' |
+------------+--------+-------+---------+-----------+
| 0x610000   | 610000 |     0 |       0 |         1 |
+------------+--------+-------+---------+-----------+
1 row in set (0.00 sec)

保存BINARY值时，在值的最后通过填充“0x00”（零字节）以达到指定的字段定义长度。

3.3ENUM类型

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

示例：往表t中插入几条记录来看看ENUM的使用方法。

mysql> CREATE TABLE t(gender enum('M','F'));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t VALUES('M'),('1'),('f'),(NULL);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t;
+--------+
| gender |
+--------+
| M      |
| M      |
| F      |
| NULL   |
+--------+
4 rows in set (0.00 sec)

从上面的例子中可以看出，ENUM类型时忽略大小写的；对于插入不再ENUM指定范围内的值时，并没有返回警告，而是插入了enum('M','F')中的第一个值“M”，这一点需要特别注意。另外ENUM类型只允许从值集合中选取单个值，而不能一次取多个值。

3.4SET类型

SET和ENUM类型非常相似，也是一个字符串对象，可以包含0~64个成员，1~8成员的集合，占1个字节；9~16成员的集合，占2个字节；17~24成员的集合，占3个字节；25~32成员的集合，占4个字节；33~64成员的集合，占8个字节。

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

示例：

mysql> CREATE TABLE t(col SET ('a','b','c','d'));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t VALUES('a,b'),('a,d,a'),('a,b'),('a,c'),('a');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t;
+------+
| col  |
+------+
| a,b  |
| a,d  |
| a,b  |
| a,c  |
| a    |
+------+
5 rows in set (0.00 sec)

SET类型可以从允许值集合中任意选择1个或多个元素进行组合，对于超出允许值范围的值不允许插入到SET类型列中。包含重复成员的集合将只取一次，这一点需要特别注意。

3.5 JSON类型

JSON是JavaScript Object Notation的缩写，是一种超级轻量级的数据交换格式。

自从MySQL5.7.8之后，MySQL开始支持JSON类型，在此之前，通常使用VARCHAR或TEXT来保存JSON格式数据。JSON类型比字符型有如下优点：

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

（2）优化的存储格式，存储在JSON列中的JSON数据被转换为内部的存储格式，允许快速读取。

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

简单说，JSON实际就是JavaScript的一个子集，支持的数据类型包括NUMBER、STRING、BOOLEAN、NULL、ARRAY、OBJECT共六种，一个JSON中的元素可以使这6种类型元素的任意组合，其中BOOLEAN使用true/false的字面值文本表示；null使用null的文本表示；字符串和日期类型都用双引号引起来表示；ARRAY要用中括号引起来；OBJECT保存的KV对要用大括号引起来，其中的K也要用双引号引起来。

下面是几个格式的正确例子。

["abc",10,null,true,false]
{"k1":"value","k2":10}
["12:18:29.0000000","2015-07-29","2015-07-29 12:18:29.000000"]

ARRAY和OBJECT也可以嵌套引用，比如：

[99,{"id":"HK500","cost":75.99},["hot","cold"]]
{"k1":"value","k2":[10,20]}

示例：JSON在MySQL中的使用。

mysql> CREATE TABLE t1(id1 json);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 VALUES('{"age":20,"time":"2018-07-14 10:52:00"}');
Query OK, 1 row affected (0.01 sec)


mysql> INSERT INTO t1 VALUES('[1,2,');
ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 5 in value for column 't1.id1'.

mysql> SELECT JSON_TYPE('"abc"') js1,JSON_TYPE('[1,2,"abc"]') js2,JSON_TYPE('{"k1":"value"}') js3;
+--------+-------+--------+
| js1    | js2   | js3    |
+--------+-------+--------+
| STRING | ARRAY | OBJECT |
+--------+-------+--------+
1 row in set (0.00 sec)

插入有语法的JSON数据，会直接报错。

通过JSON_TYPE函数可以看到插入的JSON数据是哪种类型。

JSON数据类型对于大小写是敏感的，JSON对大小写敏感是因为JSON的默认排序规则是utf8mb4_bin。'x'和'X'是不同的两个JSON数据，常见的null、true、false必须是小写的才合法。可以通过JSON_VALID函数判断一个JSON数据是否合法。

mysql> SELECT JSON_VALID('null') n1,JSON_VALID('NULL') n2,JSON_VALID('false') f1,JSON_VALID('FALSE') f2;
+------+------+------+------+
| n1   | n2   | f1   | f2   |
+------+------+------+------+
|    1 |    0 |    1 |    0 |
+------+------+------+------+
1 row in set (0.00 sec)

当JSON数据的value中字符串value中包括双引号或单引号时，则插入时需要加反斜线进行转义。

例如：

显式插入：

mysql> INSERT INTO t1 VALUES(JSON_OBJECT("name","ab\"c"));
Query OK, 1 row affected (0.01 sec)

隐式插入：


mysql> INSERT INTO t1 VALUES('{"name":"ab\"c"}');
ERROR 3140 (22032): Invalid JSON text: "Missing a comma or '}' 
after an object member." at position 12 in value for column 't1.id1'.

mysql> INSERT INTO t1 VALUES('{"name":"ab\\"c"}');
Query OK, 1 row affected (0.00 sec)

隐式插入时，需要多加一个反斜线才可以正常识别。

MySQL对JSON的存储做了一些限制，JSON列不可有默认值，且文本的最大长度取决于系统常量：max_allowed_packet。该值仅在服务器进行存储时进行限制，在内存中进行计算时是允许超过该值的。

四、MySQL中的运算符

MySQL支持多种类型的运算符，这些运算符可以用来连接表达式的项。

运算符的类型主要包括算术运算符、比较运算符、逻辑运算符和位运算符。

1.算术运算符

表4-1 MySQL中支持的算术运算符
运算符作用
+加法
-减法
*乘法
/, DIV除法，返回商
%, MOD除法，返回余数

示例：几种算术运算符的使用方法。

mysql> SELECT 0.1+0.3333,0.1-0.3333,0.1*0.3333,1/2,1%2;
+------------+------------+------------+--------+------+
| 0.1+0.3333 | 0.1-0.3333 | 0.1*0.3333 | 1/2    | 1%2  |
+------------+------------+------------+--------+------+
|     0.4333 |    -0.2333 |    0.03333 | 0.5000 |    1 |
+------------+------------+------------+--------+------+
1 row in set (0.00 sec)

mysql> SELECT 1/0,100%0;
+------+-------+
| 1/0  | 100%0 |
+------+-------+
| NULL |  NULL |
+------+-------+
1 row in set, 2 warnings (0.00 sec)

mysql> SELECT 3%2,MOD(3,2);
+------+----------+
| 3%2  | MOD(3,2) |
+------+----------+
|    1 |        1 |
+------+----------+
1 row in set (0.00 sec)

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

模运算的另一种表达方式MOD(a,b)与a%b效果一样。

2.比较运算符

当使用SELECT语句进行查询时，MySQL允许用户对表达式的左边操作数和右边操作数进行比较，比较结果为真时则返回1，为假则返回0，比较结果不确定则返回NULL。

表4-2 MySQL支持的比较运算符
运算符作用
=等于
<>或!=不等于
<=>NULL安全的等于（NULL-safe）
<小于
<=小于等于
>大于
>=大于等于
BETWEEN存在于指定范围
IN存在于指定集合
IS NULL为NULL
IS NOT NULL不为NULL
LIKE通配符匹配
REGEXP或RLIKE正则表达式匹配

比较运算符可用于比较数字、字符串和表达式。数字作为浮点数比较，而字符串以不区分大小写的方式进行比较。

示例：各种比较运算符的应用。

mysql> SELECT 1=0,1=1,NULL=NULL;
+-----+-----+-----------+
| 1=0 | 1=1 | NULL=NULL |
+-----+-----+-----------+
|   0 |   1 |      NULL |
+-----+-----+-----------+
1 row in set (0.00 sec)

注：NULL不能用“=”比较。

mysql> SELECT 1<>0,1<>1,NULL<>NULL;
+------+------+------------+
| 1<>0 | 1<>1 | NULL<>NULL |
+------+------+------------+
|    1 |    0 |       NULL |
+------+------+------------+
1 row in set (0.00 sec)

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

mysql> SELECT 1<=>1,2<=>0,0<=>0,NULL<=>NULL;
+-------+-------+-------+-------------+
| 1<=>1 | 2<=>0 | 0<=>0 | NULL<=>NULL |
+-------+-------+-------+-------------+
|     1 |     0 |     1 |           1 |
+-------+-------+-------+-------------+
1 row in set (0.00 sec)

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

“BETWEEN”运算符的使用格式为“a BETWEEN min AND max”，当a大于等于min，且小于等于max时，返回值为1，否则返回值为0；当操作数a、min、max类型相同时，此时表达式等价于（a>=min and a<=max），当操作数类型不相同时，比较时会遵循类型转换原则进行转换后，再进行比较运算。

mysql> SELECT 10 BETWEEN 10 AND 20, 9 BETWEEN 10 AND 20;
+----------------------+---------------------+
| 10 BETWEEN 10 AND 20 | 9 BETWEEN 10 AND 20 |
+----------------------+---------------------+
|                    1 |                   0 |
+----------------------+---------------------+
1 row in set (0.00 sec)

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

mysql> SELECT 1 IN (1,2,3),'t' IN ('t','a','b','k'),0 IN (1,2);
+--------------+--------------------------+------------+
| 1 IN (1,2,3) | 't' IN ('t','a','b','k') | 0 IN (1,2) |
+--------------+--------------------------+------------+
|            1 |                        1 |          0 |
+--------------+--------------------------+------------+
1 row in set (0.00 sec)

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

mysql> SELECT 0 IS NULL,NULL IS NULL,0 IS NOT NULL,NULL IS NOT NULL;
+-----------+--------------+---------------+------------------+
| 0 IS NULL | NULL IS NULL | 0 IS NOT NULL | NULL IS NOT NULL |
+-----------+--------------+---------------+------------------+
|         0 |            1 |             1 |                0 |
+-----------+--------------+---------------+------------------+
1 row in set (0.00 sec)

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

mysql> SELECT 123456 LIKE '123%',123456 LIKE '%123%',123456 LIKE '%321%';
+--------------------+---------------------+---------------------+
| 123456 LIKE '123%' | 123456 LIKE '%123%' | 123456 LIKE '%321%' |
+--------------------+---------------------+---------------------+
|                  1 |                   1 |                   0 |
+--------------------+---------------------+---------------------+
1 row in set (0.00 sec)

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

mysql> SELECT 'abcdef' REGEXP 'ab','abcdefg' REGEXP 'k';
+----------------------+----------------------+
| 'abcdef' REGEXP 'ab' | 'abcdefg' REGEXP 'k' |
+----------------------+----------------------+
|                    1 |                    0 |
+----------------------+----------------------+
1 row in set (0.01 sec)

3.逻辑运算符

逻辑运算符又称布尔运算符，用来确定表达式的真和假。

表4-2 MySQL中的逻辑运算符
运算符作用
NOT或!逻辑非
AND或&&逻辑与
OR或||逻辑或
XOR逻辑异或

“NOT”或“!”表示逻辑非，返回和操作数相反的结果，当操作数为0（假）时，则返回值1，否则返回0。有一点除外，NOT NULL 的返回值为NULL。 

mysql> SELECT NOT 0, NOT 1,NOT NULL;
+-------+-------+----------+
| NOT 0 | NOT 1 | NOT NULL |
+-------+-------+----------+
|     1 |     0 |     NULL |
+-------+-------+----------+
1 row in set (0.00 sec)

“AND”或“&&”表示逻辑与，当所有操作数均为非零值并且不为NULL时，所得结果1；当一个或多个操作数为0时，所得结果为0。操作数中有任何一个为NULL时，所得结果为NULL。

mysql> SELECT (1 AND 1),(1 AND 0),(3 AND 1),(1 AND NULL);
+-----------+-----------+-----------+--------------+
| (1 AND 1) | (1 AND 0) | (3 AND 1) | (1 AND NULL) |
+-----------+-----------+-----------+--------------+
|         1 |         0 |         1 |         NULL |
+-----------+-----------+-----------+--------------+
1 row in set (0.00 sec)

 “OR”或“||”表示逻辑或运算，当两个操作数均为非NULL值时，如有任意一个操作数为非零值，则所得结果1，否则为0；当有一个操作数为NULL时，如另一个操作数为非零值，则所得结果1，否则结果为NULL；当两个操作数都为NULL时，所得结果为NULL。

mysql> SELECT (1 OR 0),(0 OR 0),(1 OR NULL),(1 OR 1),(NULL OR NULL);
+----------+----------+-------------+----------+----------------+
| (1 OR 0) | (0 OR 0) | (1 OR NULL) | (1 OR 1) | (NULL OR NULL) |
+----------+----------+-------------+----------+----------------+
|        1 |        0 |           1 |        1 |           NULL |
+----------+----------+-------------+----------+----------------+
1 row in set (0.00 sec)

“XOR”表示逻辑异或运算，当任意一个操作数为NULL时，所得结果为NULL；对于非NULL的操作数，如果两个的逻辑真假值相异，则返回结果1，否则为0。

mysql> SELECT 1 XOR 1,0 XOR 0, 1 XOR 0,0 XOR 1,NULL XOR NULL;
+---------+---------+---------+---------+---------------+
| 1 XOR 1 | 0 XOR 0 | 1 XOR 0 | 0 XOR 1 | NULL XOR NULL |
+---------+---------+---------+---------+---------------+
|       0 |       0 |       1 |       1 |          NULL |
+---------+---------+---------+---------+---------------+
1 row in set (0.00 sec)

4.位运算符

位运算是将给定的操作数转化为二进制后，对各个操作数的每一位都进行制定的逻辑运算，得到的二进制结果转化为十进制数后就是位运算的结果。

表4-4 MySQL支持的位运算符
运算符作用
&位与（位 AND）
|

位或（位 OR）

^位异或（位 XOR）
~位取反
>>位右移
<<位左移

位运算符中的位与、位或和前面介绍的逻辑与、逻辑或非常相似。

mysql> SELECT 2&3;
+-----+
| 2&3 |
+-----+
|   2 |
+-----+
1 row in set (0.00 sec)

mysql> SELECT 2&3&4;
+-------+
| 2&3&4 |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

mysql> SELECT 2|3;
+-----+
| 2|3 |
+-----+
|   3 |
+-----+
1 row in set (0.00 sec)

mysql> SELECT 2^3;
+-----+
| 2^3 |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)

“位取反”对操作数的二进制位做NOT操作，这里的操作数只能是一位。

mysql> SELECT ~1,~18446744073709551614;
+----------------------+-----------------------+
| ~1                   | ~18446744073709551614 |
+----------------------+-----------------------+
| 18446744073709551614 |                     1 |
+----------------------+-----------------------+
1 row in set (0.00 sec)

1的位取反为何是这么大的数？在MySQL中，常量数字默认以8个字节来表示，8个字节就是64位，常量1的二进制表示为63个“0”加1个“1”，位取反后就是63个“1”加1个“0”，转换为二进制后就是18446744073709551614。

“位右移”对左操作数向右移动右操作数指定的位数，例如100>>3，就是对100的二进制数0001100100右移3位，左边补0，结果是0000001100，转换为十进制数是12。

mysql> SELECT 100>>3;
+--------+
| 100>>3 |
+--------+
|     12 |
+--------+

“位左移”对左操作数向左移动右操作数指定的位数，例如100<<3，就是对100的二进制数0001100100左移3位，右边补0，结果是1100100000，转换为十进制数是800。

mysql> SELECT 100<<3;
+--------+
| 100<<3 |
+--------+
|    800 |
+--------+
1 row in set (0.00 sec)

5.运算符的优先级

将各种运算符进行混合运算时，应当遵循优先级从高到低的顺序。

表4-5 MySQL中的运算符优先级(由低到高）
优先级顺序运算符
1:=
2||、OR、XOR
3&&、AND
4NOT
5BETWEEN、CASE、WHEN、THEN和ELSE
6=、<=>、>=、>、<=、<、<>、!=、IS、LIKE、REGEXP和IN
7|
8&
9<<和>>
10-和+
11*、/、DIV、%和MOD
12^
13-（一元减号）和~（一元比特反转）
14!

实际应用中只需借用“()”将需要的优先操作括起来，既起到了优先的作用，也方便其他用户理解。

Zoey_ov
关注
Mysql学习笔记（全）
m0_60264772的博客
12-07 2万+
目录 MyISAM和InnoDB 性能下降SQL慢的原因： Mysql执行顺序 SQLJoin 索引 索引的优劣 索引分类 mysql索引结构 那些情况建索引 哪些情况不要建索引 性能分析 Extra 索引优化 索引口诀 索引案例 Order By 索引优化 exsites Mysql慢查询日志命令 函数操作批量插入数据 show Profile分析sql 全局查询日志 Mysql锁 锁命令 表锁：读锁 表锁：写锁 行锁 事务 行锁测试 间隙锁 行
mysql高级篇学习笔记
java
07-28 7454
尚硅谷宋红康康师傅mysql高级篇学习
MySQL学习笔记
weixin_67774087的博客
05-19 2360
数据库是一个用于存储和管理数据的仓库。数据按照特定的格式存储，可以对数据库中的数据进行增加、修改、删除和查询操作。数据库的本质是一个文件系统，按照一定的逻辑结构组织数据，以方便高效地访问和维护。而MySQL是一种较为常见的数据库，较为常见的数据库还有Oracle、DB2、SQLServer、SQLite等。数据库管理系统（DataBase Management System，DBMS）是一个操作和管理数据库的软件。它用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。
MySQL学习笔记8——游标
qq_44778824的博客
04-24 2233
本节学习了游标的使用方法，包括在存储过程中使用游标的4个步骤，分别是定义游标、打开游标、读取游标数据和关闭游标。除此之外，还介绍了经常与游标结合使用的流程控制语句，包括循环语句LOOP、WHILE和REPEAT；条件判断语句IF和CASE；还有跳转语句LEAVE和ITERATE。
尚硅谷MySQL学习笔记
IAMLSL的博客
02-11 2万+
MySQL笔记目录一、MySQL数据库基础篇大纲1. 数据库概述与MySQL安装篇第01章：数据库概述第02章：MySQL环境搭建2. SQL之SELECT使用篇第03章：基本的SELECT语句第04章：运算符第05章：排序与分页第06章：多表查询第07章：单行函数第08章：聚合函数第09章：子查询3. SQL之DDL、DML、DCL使用篇第10章：创建和管理表第11章：数据处理之增删改第12章：MySQL数据类型精讲第13章：约束4. 其它数据库对象篇第14章：视图第15章：存储过程与函数第16章：变量、
MYSQL学习笔记-SQL
Hhg66hh的博客
01-11 521
MySQL学习笔记5——函数和索引
qq_44778824的博客
04-22 2539
- 删除索引DROP INDEX 索引名 ON 表名；--删除主键索引ALTER TABLE 表名 DROP PRIMARY KEY;索引的成本存储空间的开销，是指索引|需要单独占用存储空间。数据操作上的开销，是指一旦数据表有变动，无论是插入一条新数据，还是删除一条旧的数据，甚至是修改数据，如果涉及索引字段，都需要对索引本身进行修改，以确保索弓|能够指向正确的记录。因此，索引也不是越多越好，创建索引有存储开销和操作开销，需要综合考虑。
MySQL学习笔记整理（上部）
霁晨晨晨的博客
05-01 3万+
以下是我整理了关于MySQL数据库的常用命令和使用场景，一些基本的概念，比如索引、约束和事务，以及常见的问题和解决方案，欢迎大家浏览并留言，若有错误的地方请大家指正。
MySQL学习笔记整理（下部）
热门推荐
霁晨晨晨的博客
05-01 3万+
以下是我整理了关于MySQL数据库的常用命令和使用场景，一些基本的概念，比如索引、约束和事务，以及常见的问题和解决方案，欢迎大家浏览并留言，若有错误的地方请大家指正。
数据库mysql学习笔记
m0_53853047的博客
09-18 1837
数据库mysql学习笔记
MySQL学习笔记 MySQL学习笔记
09-28
MySQL是世界上最受欢迎的关系型数据库管理系统之一，尤其在Web应用...学习MySQL不仅涉及理论知识，还需要大量的实践操作来熟悉其语法和功能。掌握MySQL能让你在数据管理、Web开发和系统集成等领域具备更强的竞争力。
MySQL学习笔记.zip
02-23
这份“MySQL学习笔记”将引导我们深入理解其核心概念和实用技能。 一、MySQL简介 MySQL是一个开源、免费的数据库系统，由瑞典的MySQL AB公司开发，后被Oracle公司收购。它的设计目标是速度、可移植性和简洁性，支持...
数据库 MySQL 学习笔记高级篇.md
09-01
数据库 MySQL 学习笔记高级篇.md
Mysql学习笔记.pdf
12-24
MySQL 是一款广泛使用的开源关系型数据库管理系统，其学习笔记涵盖了多个关键知识点。以下是对这些知识点的详细解释： 1. **MySQL 体系架构** - **网络连接层**：处理客户端的连接请求，包括连接管理、认证和安全...
MySQL中表的约束
最新发布
pouop的博客
10-13 969
表中一定要有各种约束，通过约束，让我们来插入数据库中的数据是符合预期的。约束本质是通过技术手段，倒逼程序员插入正确的数据；反过来，站在MySQL的角度来单，内部已经插进来的数据，都i是符合数据约束的数据。约束的最终目标：保证数据完整性和可预期性为了完成以上的所有目标，就需要除了数据类型自带的范围约束以外的更多约束。
【MySQL】入门篇—SQL基础：SQL语言概述
thinking_chou的专栏
10-12 1028
数据定义语言（DDL）用于定义数据库结构，包括创建、修改和删除数据库对象（如表、索引等）。示例：创建用户表表示创建一个名为users的表。定义了一个自增的主键列。定义了一个不允许为空的用户名列。定义了一个不允许为空且唯一的邮箱列。
MYSQL-windows安装配置两个或多个版本MYSQL
qq_35976548的博客
10-12 1295
windows安装配置两个或多个版本MYSQL。安装第一个mysql很简单，这里不再赘述。主要说说第二个怎么安装，服务怎么配置。
数据库-01MYSQL-001MySQL知识点查漏补缺
10-12 280
mysql数据库知识点锦集
MySQL学习笔记：从基础到高级
"一千行+MySQL+学习笔记.pdf" MySQL是一种广泛应用的关系型数据库管理系统（RDBMS），它以其高效、稳定和易用性受到广大开发者的青睐。这份学习笔记涵盖了MySQL的多个关键知识点，包括基本的SQL语句、索引、数据...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值