当前位置： 首页 > news >正文

mysql数据类型和常用函数

news 2025/6/26 4:07:07

目录

1.整型

1.1参数signed和unsigned

1.2参数zerofill

1.3参数auto_increment

2.数字类型 

2.1floor()向下取整

2.2随机函数rand()

2.3重复函数repeat()

3.字符串类型

3.1length()查看字节长度，char_length()查看字符长度 

3.2字符集

3.2.1查看默认字符集

3.2.2查看已经创建的数据库的字符集

3.2.3修改已经创建的数据库的字符集

3.2.4修改mysql默认字符集

3.2.5两条转换表字符集的语句

3.2.6查看支持的字符集

3.3hex()函数

3.4cast()函数

3.5排序规则collation

3.6对于密码的加密，md5()函数

3.7upper()和lower()函数

3.8concat()和concat_ws()函数

3.9lpad()和rpad()函数

4.enum&set类型

5.时间类型

5.1日期函数

​编辑 5.1.1now()函数

5.1.2now()和sysdate()的区别

5.1.3date_add()和date_sub()函数

5.1.4date_format()函数

6.json类型

6.1json类型相关函数

6.1.1json_extract()函数

 6.1.2json_remove()函数

mysql的数据类型有：整型、数字类型、字符串类型、日期类型、json类型等主要类型

1.整型

1.1参数signed和unsigned

可以创建有符号(signed)和无符号(unsigned)的整型类型 

(root@localhost) [(none)]> use python;
Database changed
(root@localhost) [python]> create table z (a int unsigned,b tinyint signed);
Query OK, 0 rows affected (0.58 sec)(root@localhost) [python]> insert into z values(1,1);
Query OK, 1 row affected (0.13 sec)(root@localhost) [python]> insert into z values(-1,1);
ERROR 1264 (22003): Out of range value for column 'a' at row 1
(root@localhost) [python]> insert into z values(1,-1);
Query OK, 1 row affected (0.05 sec)(root@localhost) [python]> select * from z;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    1 |   -1 |
+------+------+
2 rows in set (0.00 sec)

1.2参数zerofill

查看创建表ddl有int(10)和tinyint(4)，其中10和4代表显示的数值总长度，长度不够用0填充

(root@localhost) [python]> show create table z\G
*************************** 1. row ***************************Table: z
Create Table: CREATE TABLE `z` (`a` int(10) unsigned DEFAULT NULL,`b` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.04 sec)(root@localhost) [python]> alter table z change column a a int unsigned zerofill;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0(root@localhost) [python]> select * from z;
+------------+------+
| a          | b    |
+------------+------+
| 0000000001 |    1 |
| 0000000001 |   -1 |
+------------+------+
2 rows in set (0.00 sec)(root@localhost) [python]> show create table z\G
*************************** 1. row ***************************Table: z
Create Table: CREATE TABLE `z` (`a` int(10) unsigned zerofill DEFAULT NULL,`b` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

可以修改显示的总长度

(root@localhost) [python]> alter table z change column a a int(4) unsigned zerofill;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0(root@localhost) [python]> select * from z;
+------+------+
| a    | b    |
+------+------+
| 0001 |    1 |
| 0001 |   -1 |
+------+------+
2 rows in set (0.00 sec)(root@localhost) [python]> show create table z\G
*************************** 1. row ***************************Table: z
Create Table: CREATE TABLE `z` (`a` int(4) unsigned zerofill DEFAULT NULL,`b` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

可以正常插入比显示长度更大的值

(root@localhost) [python]> insert into z values(20000,2);
Query OK, 1 row affected (0.00 sec)(root@localhost) [python]> select * from z;
+-------+------+
| a     | b    |
+-------+------+
|  0001 |    1 |
|  0001 |   -1 |
| 20000 |    2 |
+-------+------+
3 rows in set (0.00 sec)

1.3参数auto_increment

设置该参数，主键自动递增，select last_insert_id()查询上一次自增id值是多少，表数据量大主键自增字段建议用bigint类型

(root@localhost) [python]> truncate table z;
Query OK, 0 rows affected (0.13 sec)(root@localhost) [python]> alter table z change column a a int auto_increment primary key;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0(root@localhost) [python]> show create table z\G
*************************** 1. row ***************************Table: z
Create Table: CREATE TABLE `z` (`a` int(11) NOT NULL AUTO_INCREMENT,`b` tinyint(4) DEFAULT NULL,PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)(root@localhost) [python]> insert into z values(null,10);
Query OK, 1 row affected (0.01 sec)(root@localhost) [python]> insert into z values(null,20);
Query OK, 1 row affected (0.01 sec)(root@localhost) [python]> insert into z(b) values(30);
Query OK, 1 row affected (0.05 sec)(root@localhost) [python]> insert into z values(40);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
(root@localhost) [python]> select * from z;
+---+------+
| a | b    |
+---+------+
| 1 |   10 |
| 2 |   20 |
| 3 |   30 |
+---+------+
3 rows in set (0.00 sec)(root@localhost) [python]> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                3 |
+------------------+
1 row in set (0.06 sec)

 自增字段值在不重启数据库的情况下不会回溯，只会越来越大

(root@localhost) [python]> delete from z where b=30;
Query OK, 1 row affected (0.06 sec)(root@localhost) [python]> insert into z values(null,30);
Query OK, 1 row affected (0.04 sec)(root@localhost) [python]> select * from z;
+---+------+
| a | b    |
+---+------+
| 1 |   10 |
| 2 |   20 |
| 4 |   30 |
+---+------+
3 rows in set (0.00 sec)

自增字段在删除select last_insert_id()的值后，重启数据库会回溯，查看ddl，AUTO_INCREMENT=7

(root@localhost) [python]> select * from z;
+---+------+
| a | b    |
+---+------+
| 1 |   10 |
| 2 |   20 |
| 5 |   40 |
| 6 |   30 |
+---+------+
4 rows in set (0.00 sec)(root@localhost) [python]> show create table z\G
*************************** 1. row ***************************Table: z
Create Table: CREATE TABLE `z` (`a` int(11) NOT NULL AUTO_INCREMENT,`b` tinyint(4) DEFAULT NULL,PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)(root@localhost) [python]> delete from z where a=6;
Query OK, 1 row affected (0.01 sec)(root@localhost) [python]> delete from z where a=5;
Query OK, 1 row affected (0.01 sec)(root@localhost) [python]> select * from z;
+---+------+
| a | b    |
+---+------+
| 1 |   10 |
| 2 |   20 |
+---+------+
2 rows in set (0.00 sec)

删除a=6、a=5后重启数据库查看ddl，AUTO_INCREMENT=3，此时就回溯了，再次插入数据a=3、a=4就回来了，线上删除的值就又出现了，会乱掉，这个问题在mysql8.0得到解决

(root@localhost) [python]> show create table z\G
*************************** 1. row ***************************Table: z
Create Table: CREATE TABLE `z` (`a` int(11) NOT NULL AUTO_INCREMENT,`b` tinyint(4) DEFAULT NULL,PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)(root@localhost) [python]> select * from z;
+---+------+
| a | b    |
+---+------+
| 1 |   10 |
| 2 |   20 |
+---+------+
2 rows in set (0.00 sec)(root@localhost) [python]> insert into z values(null,30);
Query OK, 1 row affected (0.00 sec)(root@localhost) [python]> insert into z values(null,40);
Query OK, 1 row affected (0.01 sec)(root@localhost) [python]> select * from z;
+---+------+
| a | b    |
+---+------+
| 1 |   10 |
| 2 |   20 |
| 3 |   30 |
| 4 |   40 |
+---+------+
4 rows in set (0.00 sec)(root@localhost) [python]>

mysql8.0解决自增字段回溯问题，AUTO_INCREMENT值持久化

[root@localhost ~]# mysql -S/tmp/mysql.sock80 -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.35 MySQL Community Server - GPLCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.(root@localhost) [(none)]> create database zs;
Query OK, 1 row affected (0.42 sec)(root@localhost) [(none)]> use zs
Database changed
(root@localhost) [zs]> create table z (a bigint auto_increment primary key,b tinyint);
Query OK, 0 rows affected (1.36 sec)(root@localhost) [zs]> show create table z\G;
*************************** 1. row ***************************Table: z
Create Table: CREATE TABLE `z` (`a` bigint NOT NULL AUTO_INCREMENT,`b` tinyint DEFAULT NULL,PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.22 sec)ERROR: 
No query specified(root@localhost) [zs]> insert into z values(null,10);
Query OK, 1 row affected (0.08 sec)(root@localhost) [zs]> insert into z values(null,20);
Query OK, 1 row affected (0.05 sec)(root@localhost) [zs]> insert into z values(null,30);
Query OK, 1 row affected (0.05 sec)(root@localhost) [zs]> insert into z values(null,40);
Query OK, 1 row affected (0.01 sec)(root@localhost) [zs]> show create table z\G;
*************************** 1. row ***************************Table: z
Create Table: CREATE TABLE `z` (`a` bigint NOT NULL AUTO_INCREMENT,`b` tinyint DEFAULT NULL,PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)ERROR: 
No query specified(root@localhost) [zs]> select * from z;
+---+------+
| a | b    |
+---+------+
| 1 |   10 |
| 2 |   20 |
| 3 |   30 |
| 4 |   40 |
+---+------+
4 rows in set (0.01 sec)(root@localhost) [zs]> delete from z where a=4;
Query OK, 1 row affected (0.06 sec)(root@localhost) [zs]> delete from z where a=3;
Query OK, 1 row affected (0.05 sec)(root@localhost) [zs]> select * from z;
+---+------+
| a | b    |
+---+------+
| 1 |   10 |
| 2 |   20 |
+---+------+
2 rows in set (0.00 sec)(root@localhost) [zs]> show create table z\G;
*************************** 1. row ***************************Table: z
Create Table: CREATE TABLE `z` (`a` bigint NOT NULL AUTO_INCREMENT,`b` tinyint DEFAULT NULL,PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)ERROR: 
No query specified(root@localhost) [zs]> exit
Bye
[root@localhost ~]# mysqld_multi stop 80
[root@localhost ~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld32 is not running
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld80 is not running
[root@localhost ~]# mysqld_multi start 80
[root@localhost ~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld32 is not running
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld80 is running
[root@localhost ~]# mysql -S/tmp/mysql.sock80 -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.35 MySQL Community Server - GPLCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.(root@localhost) [(none)]> use zs;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
(root@localhost) [zs]> show create table z\G;
*************************** 1. row ***************************Table: z
Create Table: CREATE TABLE `z` (`a` bigint NOT NULL AUTO_INCREMENT,`b` tinyint DEFAULT NULL,PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)ERROR: 
No query specified(root@localhost) [zs]> select * from z;
+---+------+
| a | b    |
+---+------+
| 1 |   10 |
| 2 |   20 |
+---+------+
2 rows in set (0.00 sec)(root@localhost) [zs]> insert into z values(null,30);
Query OK, 1 row affected (0.01 sec)(root@localhost) [zs]> insert into z values(null,40);
Query OK, 1 row affected (0.00 sec)(root@localhost) [zs]> select * from z;
+---+------+
| a | b    |
+---+------+
| 1 |   10 |
| 2 |   20 |
| 5 |   30 |
| 6 |   40 |
+---+------+
4 rows in set (0.00 sec)(root@localhost) [zs]>

int类型总结

  • 推荐不要使用UNSIGNED
  • 范围本质上没有大的改变
  • UNSIGNED可能会有溢出现象发生
  • 自增INT类型主键建议使用BIGINT

2.数字类型 

一般建议用decimal 

2.1floor()向下取整

(root@localhost) [(none)]> select floor(1.5);
+------------+
| floor(1.5) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)(root@localhost) [(none)]> select floor(1.9);
+------------+
| floor(1.9) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)(root@localhost) [(none)]> select floor(-1.9);
+-------------+
| floor(-1.9) |
+-------------+
|          -2 |
+-------------+
1 row in set (0.00 sec)

2.2随机函数rand()

(root@localhost) [(none)]> select rand() ;
+-------------------+
| rand()            |
+-------------------+
| 0.590530601880076 |
+-------------------+
1 row in set (0.00 sec)(root@localhost) [(none)]> select rand() ;
+--------------------+
| rand()             |
+--------------------+
| 0.1034116913596277 |
+--------------------+
1 row in set (0.00 sec)

1~100随机整数

(root@localhost) [(none)]> select floor(1+rand()*99);
+--------------------+
| floor(1+rand()*99) |
+--------------------+
|                 74 |
+--------------------+
1 row in set (0.00 sec)(root@localhost) [(none)]> select floor(1+rand()*99);
+--------------------+
| floor(1+rand()*99) |
+--------------------+
|                 42 |
+--------------------+
1 row in set (0.00 sec)(root@localhost) [(none)]> select floor(1+rand()*99);
+--------------------+
| floor(1+rand()*99) |
+--------------------+
|                 85 |
+--------------------+
1 row in set (0.00 sec)

2.3重复函数repeat()

(root@localhost) [(none)]> select repeat(1,3);
+-------------+
| repeat(1,3) |
+-------------+
| 111         |
+-------------+
1 row in set (0.00 sec)(root@localhost) [(none)]> select repeat(32,3);
+--------------+
| repeat(32,3) |
+--------------+
| 323232       |
+--------------+
1 row in set (0.00 sec)(root@localhost) [(none)]> select repeat('b',5);
+---------------+
| repeat('b',5) |
+---------------+
| bbbbb         |
+---------------+
1 row in set (0.00 sec)(root@localhost) [(none)]> select repeat('ba',4);
+----------------+
| repeat('ba',4) |
+----------------+
| babababa       |
+----------------+
1 row in set (0.00 sec)

字符串或数字变长

(root@localhost) [mysql]> select repeat('a',floor(1+rand()*99));
+-----------------------------------------------------------------------------------------------------+
| repeat('a',floor(1+rand()*99))                                                                      |
+-----------------------------------------------------------------------------------------------------+
| aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)(root@localhost) [mysql]> select repeat('a',floor(1+rand()*99));
+--------------------------------------------+
| repeat('a',floor(1+rand()*99))             |
+--------------------------------------------+
| aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
+--------------------------------------------+
1 row in set (0.00 sec)(root@localhost) [mysql]> select repeat('b1',floor(1+rand()*20));
+---------------------------------+
| repeat('b1',floor(1+rand()*20)) |
+---------------------------------+
| b1b1b1                          |
+---------------------------------+
1 row in set (0.00 sec)(root@localhost) [mysql]> select repeat('b1',floor(1+rand()*20));
+---------------------------------+
| repeat('b1',floor(1+rand()*20)) |
+---------------------------------+
| b1b1b1b1b1b1b1b1b1              |
+---------------------------------+
1 row in set (0.00 sec)

3.字符串类型

char(n)和varchar(n)中的n代表字符，有字符集

(root@localhost) [python]> drop table z;
Query OK, 0 rows affected (0.72 sec)(root@localhost) [python]> create table z (a char(1),b varchar(3),c binary(1),d varbinary(3));
Query OK, 0 rows affected (0.02 sec)(root@localhost) [python]> insert into z values('a','bbb','c','ddd');
Query OK, 1 row affected (0.01 sec)(root@localhost) [python]> select * from z;
+------+------+------+------+
| a    | b    | c    | d    |
+------+------+------+------+
| a    | bbb  | c    | ddd  |
+------+------+------+------+
1 row in set (0.00 sec)(root@localhost) [python]> show create table z\G
*************************** 1. row ***************************Table: z
Create Table: CREATE TABLE `z` (`a` char(1) DEFAULT NULL,`b` varchar(3) DEFAULT NULL,`c` binary(1) DEFAULT NULL,`d` varbinary(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)(root@localhost) [python]> insert into z values('紫','bbc','x','ooo');
ERROR 1366 (HY000): Incorrect string value: '\xE7\xB4\xAB' for column 'a' at row 1
(root@localhost) [python]> alter table z convert to charset utf8mb4;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0(root@localhost) [python]> show create table z\G
*************************** 1. row ***************************Table: z
Create Table: CREATE TABLE `z` (`a` char(1) DEFAULT NULL,`b` varchar(3) DEFAULT NULL,`c` binary(1) DEFAULT NULL,`d` varbinary(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)(root@localhost) [python]> insert into z values('紫','bbc','x','ooo');
Query OK, 1 row affected (0.00 sec)(root@localhost) [python]> insert into z values('x','bbc','紫','ooo');
ERROR 1406 (22001): Data too long for column 'c' at row 1

3.1length()查看字节长度，char_length()查看字符长度 

(root@localhost) [python]> select length('紫'),char_length('紫');
+---------------+--------------------+
| length('紫')  | char_length('紫')  |
+---------------+--------------------+
|             3 |                  1 |
+---------------+--------------------+
1 row in set (0.00 sec)

3.2字符集

mysql中的字符集最小是在列级别上

3.2.1查看默认字符集

(root@localhost) [python]> show variables like '%character%';
+--------------------------+----------------------------------------------------------------+
| Variable_name            | Value                                                          |
+--------------------------+----------------------------------------------------------------+
| character_set_client     | utf8                                                           |
| character_set_connection | utf8                                                           |
| character_set_database   | latin1                                                         |
| character_set_filesystem | binary                                                         |
| character_set_results    | utf8                                                           |
| character_set_server     | utf8mb4                                                        |
| character_set_system     | utf8                                                           |
| character_sets_dir       | /usr/local/mysql-5.7.44-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------------------+

3.2.2查看已经创建的数据库的字符集

(root@localhost) [zs]> show create database python;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| python   | CREATE DATABASE `python` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

3.2.3修改已经创建的数据库的字符集

(root@localhost) [python]> alter database python charset utf8mb4;
Query OK, 1 row affected (0.00 sec)(root@localhost) [python]> show create database python;
+----------+--------------------------------------------------------------------+
| Database | Create Database                                                    |
+----------+--------------------------------------------------------------------+
| python   | CREATE DATABASE `python` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

3.2.4修改mysql默认字符集

在my.cnf中加入参数

[mysqld]

character_set_server=utf8mb4

3.2.5两条转换表字符集的语句

alter table t charset utf8mb4;与alter table t convert to charset(或者character set) utf8mb4;的不同，前者对已经存在的列不进行字符集转换，只对新增列转换，后者是对已经存在的列和新增列都转换，旧的字符集是新的字符集的子集，在生产上要注意转换字符集是要锁表的，要小心

(root@localhost) [python]> drop table t;
Query OK, 0 rows affected (0.01 sec)(root@localhost) [python]> create table t(a varchar(10),b char(2)) charset gbk;
Query OK, 0 rows affected (0.04 sec)(root@localhost) [python]> show create table t\G;
*************************** 1. row ***************************Table: t
Create Table: CREATE TABLE `t` (`a` varchar(10) DEFAULT NULL,`b` char(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)ERROR: 
No query specified(root@localhost) [python]> alter table t charset utf8mb4;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0(root@localhost) [python]> show create table t\G;
*************************** 1. row ***************************Table: t
Create Table: CREATE TABLE `t` (`a` varchar(10) CHARACTER SET gbk DEFAULT NULL,`b` char(2) CHARACTER SET gbk DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)ERROR: 
No query specified(root@localhost) [python]> alter table t add column c varchar(11);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0(root@localhost) [python]> show create table t\G;
*************************** 1. row ***************************Table: t
Create Table: CREATE TABLE `t` (`a` varchar(10) CHARACTER SET gbk DEFAULT NULL,`b` char(2) CHARACTER SET gbk DEFAULT NULL,`c` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)ERROR: 
No query specified(root@localhost) [python]> alter table t convert to charset utf8mb4;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0(root@localhost) [python]> show create table t\G;
*************************** 1. row ***************************Table: t
Create Table: CREATE TABLE `t` (`a` varchar(10) DEFAULT NULL,`b` char(2) DEFAULT NULL,`c` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)ERROR: 
No query specified

3.2.6查看支持的字符集

show charset，Maxlen表示最大的一个字符占用多少个字节

(root@localhost) [python]> show charset;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset           | binary              |      1 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)

3.3hex()函数

查看字符的16进制

(root@localhost) [python]> select hex('a');
+----------+
| hex('a') |
+----------+
| 61       |
+----------+
1 row in set (0.00 sec)(root@localhost) [python]> select hex('ab');
+-----------+
| hex('ab') |
+-----------+
| 6162      |
+-----------+
1 row in set (0.00 sec)(root@localhost) [python]> select hex('abc');
+------------+
| hex('abc') |
+------------+
| 616263     |
+------------+
1 row in set (0.00 sec)(root@localhost) [python]> select hex('紫');
+------------+
| hex('紫')  |
+------------+
| E7B4AB     |
+------------+
1 row in set (0.00 sec)(root@localhost) [python]> select 0xE7B4AB;
+----------+
| 0xE7B4AB |
+----------+
| 紫       |
+----------+
1 row in set (0.00 sec)(root@localhost) [python]> insert into t values('aaa',0xE7B4AB,0xE7B4AB);
Query OK, 1 row affected (0.00 sec)(root@localhost) [python]> select * from t;
+------+------+------+
| a    | b    | c    |
+------+------+------+
| aaa  | 紫   | 紫   |
+------+------+------+
1 row in set (0.00 sec)

3.4cast()函数

CAST函数用于将某种数据类型的表达式显式转换为另一种数据类型。CAST()函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型。

语法

CAST(value AS datatype)

datatype参数取值 

值描述
DATE将value转换成'YYYY-MM-DD'格式
DATETIME将value转换成'YYYY-MM-DD HH：MM：SS'格式
TIME将value转换成'HH：MM：SS'格式
CHAR将value转换成CHAR(固定长度的字符串)格式
SIGNED将value转换成INT(有符号的整数)格式
UNSIGNED将value转换成INT(无符号的整数)格式
DECIMAL将value转换成FLOAT(浮点数)格式 
BINARY将value转换成二进制格式
(root@localhost) [python]> SELECT CAST('13.6' AS decimal(9,2));
+------------------------------+
| CAST('13.6' AS decimal(9,2)) |
+------------------------------+
|                        13.60 |
+------------------------------+
1 row in set (0.00 sec)(root@localhost) [python]> select cast(123 as char(1));
+----------------------+
| cast(123 as char(1)) |
+----------------------+
| 1                    |
+----------------------+
1 row in set, 1 warning (0.00 sec)(root@localhost) [python]> select cast(123 as char(10));
+-----------------------+
| cast(123 as char(10)) |
+-----------------------+
| 123                   |
+-----------------------+
1 row in set (0.00 sec)(root@localhost) [python]> select cast('a' as char(10));
+-----------------------+
| cast('a' as char(10)) |
+-----------------------+
| a                     |
+-----------------------+
1 row in set (0.01 sec)(root@localhost) [python]> select cast('a' as char(10) charset gbk);
+-----------------------------------+
| cast('a' as char(10) charset gbk) |
+-----------------------------------+
| a                                 |
+-----------------------------------+
1 row in set (0.00 sec)(root@localhost) [python]> select hex(cast('a' as char(10) charset gbk));
+----------------------------------------+
| hex(cast('a' as char(10) charset gbk)) |
+----------------------------------------+
| 61                                     |
+----------------------------------------+
1 row in set (0.00 sec)(root@localhost) [python]> select hex('a');
+----------+
| hex('a') |
+----------+
| 61       |
+----------+
1 row in set (0.00 sec)(root@localhost) [python]> select hex(cast('紫' as char(10) charset gbk));
+------------------------------------------+
| hex(cast('紫' as char(10) charset gbk))  |
+------------------------------------------+
| D7CF                                     |
+------------------------------------------+
1 row in set (0.00 sec)(root@localhost) [python]> select hex('紫');
+------------+
| hex('紫')  |
+------------+
| E7B4AB     |
+------------+
1 row in set (0.00 sec)(root@localhost) [python]> select 0xD7CF;
+--------+
| 0xD7CF |
+--------+
| ؏       |
+--------+
1 row in set (0.00 sec)(root@localhost) [python]> show create table z\G;
*************************** 1. row ***************************Table: z
Create Table: CREATE TABLE `z` (`a` char(1) DEFAULT NULL,`b` varchar(3) DEFAULT NULL,`c` binary(1) DEFAULT NULL,`d` varbinary(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)ERROR: 
No query specified(root@localhost) [python]> insert into z values('a',0xD7CF,'c',0xD7CF);
ERROR 1366 (HY000): Incorrect string value: '\xD7\xCF' for column 'b' at row 1
(root@localhost) [python]> insert into z values('a',0xE7B4AB,'c',0xD7CF);
Query OK, 1 row affected (0.00 sec)(root@localhost) [python]> select * from z;
+------+------+------+------+
| a    | b    | c    | d    |
+------+------+------+------+
| a    | bbb  | c    | ddd  |
| 紫   | bbc  | x    | ooo  |
| a    | 紫   | c    | ؏     |
+------+------+------+------+
3 rows in set (0.00 sec)

从上面看出，varbinary类型不会检测对应的字符是否存在，只会去存16进制的这个值，varchar类型会去检测16进制的值对应的字符是否存在

3.5排序规则collation

(root@localhost) [(none)]> select 'a' = 'a';
+-----------+
| 'a' = 'a' |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)(root@localhost) [(none)]> select 'a' = 'a    ';
+---------------+
| 'a' = 'a    ' |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)(root@localhost) [(none)]> select 'a' = 'A';
+-----------+
| 'a' = 'A' |
+-----------+
|         1 |
+-----------+
1 row in set (0.01 sec)(root@localhost) [(none)]> select 'a' = 'A    ';
+---------------+
| 'a' = 'A    ' |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)(root@localhost) [python]> drop table t;
Query OK, 0 rows affected (0.02 sec)(root@localhost) [python]> create table t (a char(1),unique key(a));
Query OK, 0 rows affected (0.02 sec)(root@localhost) [python]> insert into t values('a');
Query OK, 1 row affected (0.00 sec)(root@localhost) [python]> insert into t values('A');
ERROR 1062 (23000): Duplicate entry 'A' for key 'a'
(root@localhost) [python]> insert into t values('a    ');
ERROR 1062 (23000): Duplicate entry 'a' for key 'a'
(root@localhost) [python]> insert into t values('A    ');
ERROR 1062 (23000): Duplicate entry 'A' for key 'a'
(root@localhost) [python]> select * from t;
+------+
| a    |
+------+
| a    |
+------+
1 row in set (0.00 sec)

 show charset看到的有一列是Default collation，其中utf8mb4字符集对应的排序规则是utf8mb4_general_ci，ci(case insensitive)表示不区分大小写 

3.6对于密码的加密，md5()函数

(root@localhost) [python]> drop table t;
Query OK, 0 rows affected (0.01 sec)(root@localhost) [python]> create table t(password varchar(128));
Query OK, 0 rows affected (0.01 sec)(root@localhost) [python]> insert into t values('aaa');
Query OK, 1 row affected (0.00 sec)(root@localhost) [python]> insert into t values('BBB');
Query OK, 1 row affected (0.01 sec)(root@localhost) [python]> insert into t values(md5('aaa'));
Query OK, 1 row affected (0.01 sec)(root@localhost) [python]> insert into t values(md5('BBB'));
Query OK, 1 row affected (0.00 sec)(root@localhost) [python]> select * from t;
+----------------------------------+
| password                         |
+----------------------------------+
| aaa                              |
| BBB                              |
| 47bce5c74f589f4867dbd57e9ca9f808 |
| 2bb225f0ba9a58930757a868ed57d9a3 |
+----------------------------------+
4 rows in set (0.00 sec)(root@localhost) [python]> insert into t values(md5(concat('aaa','zs')));
Query OK, 1 row affected (0.00 sec)(root@localhost) [python]> insert into t values(md5(concat('BBB','zs')));
Query OK, 1 row affected (0.01 sec)(root@localhost) [python]> select * from t;
+----------------------------------+
| password                         |
+----------------------------------+
| aaa                              |
| BBB                              |
| 47bce5c74f589f4867dbd57e9ca9f808 |
| 2bb225f0ba9a58930757a868ed57d9a3 |
| ebf93b07e04f07d5efea104226ce36f5 |
| 5812c79db401f78da7fd2aaf03f9ca97 |
+----------------------------------+
6 rows in set (0.00 sec)

密码通过加密存储，一般来说也不区分大小写

强制密码区分大小写

(root@localhost) [python]> create table y (password varchar(128) collate utf8mb4_bin,unique key(password));
Query OK, 0 rows affected (0.01 sec)(root@localhost) [python]> insert into y values('a');
Query OK, 1 row affected (0.00 sec)(root@localhost) [python]> insert into y values('A');
Query OK, 1 row affected (0.00 sec)(root@localhost) [python]> select * from y;
+----------+
| password |
+----------+
| A        |
| a        |
+----------+
2 rows in set (0.00 sec)

3.7upper()和lower()函数

upper()字符串转换成大写，lower()字符串转换成小写

(root@localhost) [python]> select upper('abC');
+--------------+
| upper('abC') |
+--------------+
| ABC          |
+--------------+
1 row in set (0.01 sec)(root@localhost) [python]> select lower('aBC');
+--------------+
| lower('aBC') |
+--------------+
| abc          |
+--------------+
1 row in set (0.01 sec)

3.8concat()和concat_ws()函数

concat()直接把字符串拼接起来，concat_ws()通过指定的符号把字符串拼接起来

(root@localhost) [python]> select concat('a','b','ccc',123);
+---------------------------+
| concat('a','b','ccc',123) |
+---------------------------+
| abccc123                  |
+---------------------------+
1 row in set (0.00 sec)(root@localhost) [python]> select concat_ws('!','a','b','ccc',123);
+----------------------------------+
| concat_ws('!','a','b','ccc',123) |
+----------------------------------+
| a!b!ccc!123                      |
+----------------------------------+
1 row in set (0.00 sec)

3.9lpad()和rpad()函数

lpad()左填充，rpad()右填充

(root@localhost) [python]> select lpad('aaa',8,'!');
+-------------------+
| lpad('aaa',8,'!') |
+-------------------+
| !!!!!aaa          |
+-------------------+
1 row in set (0.00 sec)(root@localhost) [python]> select rpad('aaa',8,'!');
+-------------------+
| rpad('aaa',8,'!') |
+-------------------+
| aaa!!!!!          |
+-------------------+
1 row in set (0.00 sec)

4.enum&set类型

枚举类型常用于性别等distinct的值不多的情况 

(root@localhost) [python]> drop table y;
Query OK, 0 rows affected (0.01 sec)(root@localhost) [python]> create table y (sex enum('male','female'));
Query OK, 0 rows affected (0.01 sec)(root@localhost) [python]> 
(root@localhost) [python]> insert into y values('male');
Query OK, 1 row affected (0.00 sec)(root@localhost) [python]> insert into y values('male11');
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
(root@localhost) [python]> insert into y values('female');
Query OK, 1 row affected (0.00 sec)(root@localhost) [python]> insert into y values('zhs');
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
(root@localhost) [python]> select * from y;
+--------+
| sex    |
+--------+
| male   |
| female |
+--------+
2 rows in set (0.01 sec)

5.时间类型

常用日期类型：datetime和timestamp 

5.1日期函数

 5.1.1now()函数

unix_timestamp(now())是1970-01-01 00:00:00到现在的秒数 

(root@localhost) [dbt3]> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-03-21 13:21:05 |
+---------------------+
1 row in set (0.00 sec)(root@localhost) [dbt3]> select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
|            1710998483 |
+-----------------------+
1 row in set (0.00 sec)(root@localhost) [dbt3]> select timestamp(now());
+---------------------+
| timestamp(now())    |
+---------------------+
| 2024-03-21 13:21:29 |
+---------------------+
1 row in set (0.00 sec)(root@localhost) [dbt3]> create table z (a datetime,b timestamp);
Query OK, 0 rows affected (0.01 sec)(root@localhost) [dbt3]> insert into z values(now(),now());
Query OK, 1 row affected (0.00 sec)(root@localhost) [dbt3]> select * from z;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2024-03-21 13:26:50 | 2024-03-21 13:26:50 |
+---------------------+---------------------+
1 row in set (0.00 sec)(root@localhost) [dbt3]> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | SYSTEM |
+---------------+--------+
1 row in set (0.00 sec)(root@localhost) [dbt3]> set time_zone = '+0:00';
Query OK, 0 rows affected (0.00 sec)(root@localhost) [dbt3]> select * from z;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2024-03-21 13:26:50 | 2024-03-21 05:26:50 |
+---------------------+---------------------+
1 row in set (0.00 sec)(root@localhost) [dbt3]> select now(10);
ERROR 1426 (42000): Too-big precision 10 specified for 'now'. Maximum is 6.
(root@localhost) [dbt3]> select now(6);
+----------------------------+
| now(6)                     |
+----------------------------+
| 2024-03-21 05:29:22.432329 |
+----------------------------+
1 row in set (0.00 sec)(root@localhost) [dbt3]> select now(3);
+-------------------------+
| now(3)                  |
+-------------------------+
| 2024-03-21 05:29:36.677 |
+-------------------------+
1 row in set (0.00 sec)

5.1.2now()和sysdate()的区别

now()表示该条sql执行开始的时间，sysdate()表示执行到该函数的时间

(root@localhost) [dbt3]> select now(),sysdate(),sleep(4),now(),sysdate();
+---------------------+---------------------+----------+---------------------+---------------------+
| now()               | sysdate()           | sleep(4) | now()               | sysdate()           |
+---------------------+---------------------+----------+---------------------+---------------------+
| 2024-03-21 05:34:46 | 2024-03-21 05:34:46 |        0 | 2024-03-21 05:34:46 | 2024-03-21 05:34:50 |
+---------------------+---------------------+----------+---------------------+---------------------+
1 row in set (4.00 sec)(root@localhost) [dbt3]> select now(6),sysdate(6);
+----------------------------+----------------------------+
| now(6)                     | sysdate(6)                 |
+----------------------------+----------------------------+
| 2024-03-21 05:35:58.930067 | 2024-03-21 05:35:58.930234 |
+----------------------------+----------------------------+
1 row in set (0.00 sec)

5.1.3date_add()和date_sub()函数

时间加减函数

(root@localhost) [dbt3]> set time_zone='+8:00';
Query OK, 0 rows affected (0.00 sec)(root@localhost) [dbt3]> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-03-21 13:41:15 |
+---------------------+
1 row in set (0.00 sec)(root@localhost) [dbt3]> select date_add(now(),interval 1 day);
+--------------------------------+
| date_add(now(),interval 1 day) |
+--------------------------------+
| 2024-03-22 13:42:18            |
+--------------------------------+
1 row in set (0.00 sec)(root@localhost) [dbt3]> select date_add(now(),interval -2 day);
+---------------------------------+
| date_add(now(),interval -2 day) |
+---------------------------------+
| 2024-03-19 13:42:33             |
+---------------------------------+
1 row in set (0.00 sec)(root@localhost) [dbt3]> select date_add(now(),interval -2 hour);
+----------------------------------+
| date_add(now(),interval -2 hour) |
+----------------------------------+
| 2024-03-21 11:43:06              |
+----------------------------------+
1 row in set (0.00 sec)(root@localhost) [dbt3]> select date_add(now(),interval -2 minute);
+------------------------------------+
| date_add(now(),interval -2 minute) |
+------------------------------------+
| 2024-03-21 13:41:25                |
+------------------------------------+
1 row in set (0.00 sec)(root@localhost) [dbt3]> select date_sub(now(),interval 2 day);
+--------------------------------+
| date_sub(now(),interval 2 day) |
+--------------------------------+
| 2024-03-19 14:45:05            |
+--------------------------------+
1 row in set (0.00 sec)(root@localhost) [dbt3]> select date_sub(now(),interval -2 day);
+---------------------------------+
| date_sub(now(),interval -2 day) |
+---------------------------------+
| 2024-03-23 14:45:10             |
+---------------------------------+
1 row in set (0.00 sec)(root@localhost) [dbt3]> select date_sub(now(),interval 4 hour);
+---------------------------------+
| date_sub(now(),interval 4 hour) |
+---------------------------------+
| 2024-03-21 10:45:42             |
+---------------------------------+
1 row in set (0.01 sec)

5.1.4date_format()函数

语法

DATE_FORMAT(date,format)

date 参数是合法的日期，format 规定日期/时间的输出格式。 可用的格式如下：

格式描述
%a缩写星期名
%b缩写月名
%c月，数值
%D带有英文前缀的月中的天
%d月的天，数值(00-31)
%e月的天，数值(0-31)
%f微秒
%H小时 (00-23)
%h小时 (01-12)
%I小时 (01-12)
%i分钟，数值(00-59)
%j年的天 (001-366)
%k小时 (0-23)
%l小时 (1-12)
%M月名
%m月，数值(00-12)
%pAM 或 PM
%r时间，12-小时（hh:mm:ss AM 或 PM）
%S秒(00-59)
%s秒(00-59)
%T时间, 24-小时 (hh:mm:ss)
%U周 (00-53) 星期日是一周的第一天
%u周 (00-53) 星期一是一周的第一天
%V周 (01-53) 星期日是一周的第一天，与 %X 使用
%v周 (01-53) 星期一是一周的第一天，与 %x 使用
%W星期名
%w周的天 （0=星期日, 6=星期六）
%X年，其中的星期日是周的第一天，4 位，与 %V 使用
%x年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y年，4 位
%y年，2 位
(root@localhost) [dbt3]> select date_format(now(),'%Y/%m/%s %H.%i.%S');
+----------------------------------------+
| date_format(now(),'%Y/%m/%s %H.%i.%S') |
+----------------------------------------+
| 2024/03/25 13.49.25                    |
+----------------------------------------+
1 row in set (0.00 sec)(root@localhost) [dbt3]> select date_format(now(),'%a');
+-------------------------+
| date_format(now(),'%a') |
+-------------------------+
| Thu                     |
+-------------------------+
1 row in set (0.00 sec)(root@localhost) [dbt3]> select date_format(now(),'%b');
+-------------------------+
| date_format(now(),'%b') |
+-------------------------+
| Mar                     |
+-------------------------+
1 row in set (0.00 sec)(root@localhost) [dbt3]> select date_format(now(),'%W');
+-------------------------+
| date_format(now(),'%W') |
+-------------------------+
| Thursday                |
+-------------------------+
1 row in set (0.00 sec)

6.json类型

 json类型跟python中的字典类似

(root@localhost) [dbt3]> create table userjson(uid bigint auto_increment primary key,data json);
Query OK, 0 rows affected (0.01 sec)(root@localhost) [dbt3]> show create table userjson\G
*************************** 1. row ***************************Table: userjson
Create Table: CREATE TABLE `userjson` (`uid` bigint(20) NOT NULL AUTO_INCREMENT,`data` json DEFAULT NULL,PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)(root@localhost) [dbt3]> INSERT INTO userjson(uid,data)VALUES (NULL,'{"name":"David","address": "Shanghai"}');
Query OK, 1 row affected (0.04 sec)(root@localhost) [dbt3]> INSERT INTO userjson(uid,data)VALUES (NULL,'{"name":"Jim","passport":"E02318883"}');
Query OK, 1 row affected (0.00 sec)(root@localhost) [dbt3]> select * from userjson;
+-----+------------------------------------------+
| uid | data                                     |
+-----+------------------------------------------+
|   1 | {"name": "David", "address": "Shanghai"} |
|   2 | {"name": "Jim", "passport": "E02318883"} |
+-----+------------------------------------------+
2 rows in set (0.00 sec)

6.1json类型相关函数

6.1.1json_extract()函数

(root@localhost) [dbt3]> select uid,json_extract(data,'$.name') from userjson;
+-----+-----------------------------+
| uid | json_extract(data,'$.name') |
+-----+-----------------------------+
|   1 | "David"                     |
|   2 | "Jim"                       |
+-----+-----------------------------+
2 rows in set (0.00 sec)(root@localhost) [dbt3]> select uid,json_unquote(json_extract(data,'$.name')) from userjson;
+-----+-------------------------------------------+
| uid | json_unquote(json_extract(data,'$.name')) |
+-----+-------------------------------------------+
|   1 | David                                     |
|   2 | Jim                                       |
+-----+-------------------------------------------+
2 rows in set (0.00 sec)(root@localhost) [dbt3]> select uid,data->'$.name' from userjson;
+-----+----------------+
| uid | data->'$.name' |
+-----+----------------+
|   1 | "David"        |
|   2 | "Jim"          |
+-----+----------------+
2 rows in set (0.00 sec)(root@localhost) [dbt3]> select uid,data->>'$.name' from userjson;
+-----+-----------------+
| uid | data->>'$.name' |
+-----+-----------------+
|   1 | David           |
|   2 | Jim             |
+-----+-----------------+
2 rows in set (0.00 sec)(root@localhost) [dbt3]> select uid,data->>'$.passport' from userjson;
+-----+---------------------+
| uid | data->>'$.passport' |
+-----+---------------------+
|   1 | NULL                |
|   2 | E02318883           |
+-----+---------------------+
2 rows in set (0.00 sec)

 6.1.2json_remove()函数

(root@localhost) [dbt3]> select * from userjson;
+-----+------------------------------------------+
| uid | data                                     |
+-----+------------------------------------------+
|   1 | {"name": "David", "address": "Shanghai"} |
|   2 | {"name": "Jim", "passport": "E02318883"} |
+-----+------------------------------------------+
2 rows in set (0.00 sec)(root@localhost) [dbt3]> select uid,json_remove(data,'$.name') from userjson;
+-----+----------------------------+
| uid | json_remove(data,'$.name') |
+-----+----------------------------+
|   1 | {"address": "Shanghai"}    |
|   2 | {"passport": "E02318883"}  |
+-----+----------------------------+
2 rows in set (0.01 sec)(root@localhost) [dbt3]> select uid,json_remove(data,'$.passport') from userjson;
+-----+------------------------------------------+
| uid | json_remove(data,'$.passport')           |
+-----+------------------------------------------+
|   1 | {"name": "David", "address": "Shanghai"} |
|   2 | {"name": "Jim"}                          |
+-----+------------------------------------------+
2 rows in set (0.00 sec)

相关文章：

mysql数据类型和常用函数

目录 1.整型 1.1参数signed和unsigned 1.2参数zerofill 1.3参数auto_increment 2.数字类型 2.1floor()向下取整 2.2随机函数rand() 2.3重复函数repeat() 3.字符串类型 3.1length()查看字节长度，char_length()查看字符长度 3.2字符集 3.2.1查看默认字符…...

编程日记 2024/3/21 15:59:20

Elastic 线下 Meetup 将于 2024 年 3 月 30 号在武汉举办

2024 Elastic Meetup 武汉站活动，由 Elastic、腾讯、新智锦绣联合举办，现诚邀广大技术爱好者及开发者参加。 活动时间 2024年3月30日 13:30-18:00 活动地点 中国武汉 武汉市江夏区腾讯大道1号腾讯武汉研发中心一楼多功能厅 13:30-14:00 入场 活动流程…...

编程日记 2024/3/21 15:58:19

线性代数在卷积神经网络（CNN）中的体现

案例：深度学习中的卷积神经网络（CNN） 在图像识别领域，卷积神经网络（Convolutional Neural Networks, CNN）是一个广泛应用深度学习模型，它在人脸识别、物体识别、医学图像分析等方面取得…...

编程日记 2024/3/21 15:57:17

服务器根据用途划分有哪几种？

随着企业需求的不同，服务器的类型也变得多种多样了，有根据机箱结构来划分的服务器类型，如机架式服务器、刀片式服务器和塔式服务器等，也有按照应用层次来划分的服务器类型，如入门级服务器和工作组服务器等。 那根据用途…...

编程日记 2024/3/21 15:53:13

linux 命令笔记：gpustat

1 命令介绍 gpustat是一个基于Python的命令行工具，它提供了一种快速、简洁的方式来查看GPU的状态和使用情况它是nvidia-smi工具的一个封装，旨在以更友好和易于阅读的格式显示GPU信息。gpustat不仅显示基本的GPU状态（如温度、GPU利用率和内存…...

编程日记 2024/3/21 15:49:08

【阅读笔记】Adaptive GPS/INS integration for relative navigation

Lee J Y, Kim H S, Choi K H, et al. Adaptive GPS/INS integration for relative navigation[J]. Gps Solutions, 2016, 20: 63-75. 用于相对导航的自适应GPS/INS集成 名词翻译 formation flying：编队飞行 摘要翻译 在编队飞行、防撞、协同定位和事故监测等许多…...

编程日记 2024/3/21 15:48:07

Java版直播商城免 费 搭 建：电商、小程序、三级分销及免 费 搭 建，平台规划与营销策略全掌握

随着互联网的快速发展，越来越多的企业开始注重数字化转型，以提升自身的竞争力和运营效率。在这个背景下，鸿鹄云商SAAS云产品应运而生，为企业提供了一种简单、高效、安全的数字化解决方案。 鸿鹄云商SAAS云产品是一种基于云计算的软…...

编程日记 2024/3/21 15:47:05

经典Bug永流传---每周一“虫”(四十五)

如果有人错过机会，多半不是机会没来，而是因为机会过来时，没有一伸手抓住它。 大写W惹的祸 前提： A账号已登录 步骤： 打开某商品链接，然后在商品的评论区任意一条评论，点击回复，回…...

编程日记 2024/3/21 15:46:04

蓝桥杯-礼物-二分查找

题目 思路 --刚开始想到暴力尝试的方法，但是N太大了，第一个测试点都超时。题目中说前k个石头的和还有后k个石头的和要小于s，在这里要能想到开一个数组来求前n个石头的总重，然后求前k个的直接将sum[i]-sum[i-k-1]就行了&#xff0…...

编程日记 2024/3/21 15:45:02

设计原则、工厂、单例模式

什么是设计模式 简单来说，设计模式就是很多程序员经过相当长的一段时间的代码实践、踩坑所总结出来的一套解决方案，这个解决方案能让我们少写一些屎山代码，能让我们写出来的代码写出来更加优雅，更加可靠。所以设计模式的好处是显而…...

编程日记 2024/3/21 15:44:01

笔记：Mysql 主从搭建

主库 创建用户并授权 create user slave identified with mysql_native_password by 123456 GRANT REPLICATION SLAVE ON *.* to slave%; FLUSH PRIVILEGES;主库配置文件 /etc/my.cnf #日志路径及文件名，目录要是mysql有权限写入 log-bin/var/lib/mysql/binlog …...

编程日记 2024/3/21 15:40:57

HTTP Error 400. The request hostname is invalid.

异常信息 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN""http://www.w3.org/TR/html4/strict.dtd"> <HTML><HEAD><TITLE>Bad Request</TITLE> <META HTTP-EQUIV"Content-Type" Content"text/html;…...

编程日记 2024/3/21 15:39:54

mysql日志( Redo Log 、Undo Log、Bin Log)

InnoDB是一个带有ACID事务支持的存储引擎&#xff0c;其中redo log和undo log是其实现原子性、一致性、隔离性和持久性&#xff08;ACID&#xff09;的重要机制。 Redo Log&#xff08;重做日志&#xff09; Redo log主要用于实现事务的持久性。它记录了后续可以用来恢复数据…...

编程日记 2024/3/21 15:36:49

HarmonyOS如何创建及调用三方库

介绍 本篇主要向开发者展示了在Stage模型中&#xff0c;如何调用已经上架到三方库中心的社区库和项目内创建的本地库。效果图如下&#xff1a; 相关概念 Navigation&#xff1a;一般作为Page页面的根容器&#xff0c;通过属性设置来展示页面的标题、工具栏、菜单。Tabs&#…...

编程日记 2024/3/21 15:33:45

我手写的轮子开源了

我手写的轮子开源了 文章目录 1.gitee坐标和地址1.1.gitee坐标1.2.gitee地址 2.github坐标和地址2.1.github坐标2.2.github地址 3.总结 1.gitee坐标和地址 1.1.gitee坐标 <dependency><groupId>io.gitee.bigbigfeifei</groupId><artifactId>es-sprin…...

编程日记 2024/3/21 15:32:44

第十九章 linux部署scrapyd

文章目录 1. linux部署python环境1. 部署python源文件环境2. 下载python3. 解压安装包4. 安装5. 配置环境变量6. 检查是否安装成功7. 准备python使用的包8. 安装scrapyd9. 配置scrapyd10. 开放6800端口 2. 部署gerapy1. 本机下载包2. 初始化3. 进入gerapy同步数据库4. 创建用户…...

编程日记 2024/3/21 15:31:43

微信打卡小程序怎么做_用户的每日习惯培养神器

微信打卡小程序&#xff1a;你的每日习惯培养神器 在这个快节奏的现代社会&#xff0c;我们每天都在忙碌中度过&#xff0c;有时候甚至会忘记自己曾经立下的那些小目标、小习惯。然而&#xff0c;随着科技的不断发展&#xff0c;微信打卡小程序的出现&#xff0c;为我们的生活…...

编程日记 2024/3/21 15:29:40

C语言数据在内存中的存储

reference n.提及&#xff0c;谈到&#xff1b;参考&#xff0c;查阅&#xff1b;&#xff08;引自书或诗歌的&#xff09;引言&#xff0c;引文&#xff1b; 引文的作者&#xff0c;参考书目&#xff1b;&#xff08;帮助或意见的&#xff09;征求&#xff0c;征询&#xff1b…...

编程日记 2024/3/21 15:27:38

管理公司员工上网行为的软件都有哪些?

随着互联网的飞速发展&#xff0c;企业面临的网络安全威胁也日益加剧。为了保护企业数据安全、提高工作效率&#xff0c;上网行为管理系统及其相关管理软件应运而生。 未来&#xff0c;随着技术的不断进步和网络安全威胁的不断演变&#xff0c;上网行为管理系统及其管理软件将不…...

编程日记 2024/3/21 15:24:32

手撕C语言题典——逆序输出

有这样一个问题&#xff1a;读入一些整数&#xff0c;逆序输出到一行中。已知的是该整数不超过100个。我们该怎么办呢&#xff1f;我们先将这些整数循环输入&#xff0c;输入每个整数之后&#xff0c;我们只能将数组存下来&#xff0c;而这个地方就是数组。 本章可能用到的知识…...

编程日记 2024/3/21 15:21:27

SciencePlots——绘制论文中的图片

文章目录 安装一、风格二、1 资源 安装 # 安装最新版 pip install githttps://github.com/garrettj403/SciencePlots.git# 安装稳定版 pip install SciencePlots一、风格 简单好用的深度学习论文绘图专用工具包–Science Plot 二、 1 资源 论文绘图神器来了&#xff1a;一行…...

编程新知 2025/6/17 19:00:28

JavaScript 中的 ES|QL：利用 Apache Arrow 工具

作者&#xff1a;来自 Elastic Jeffrey Rengifo 学习如何将 ES|QL 与 JavaScript 的 Apache Arrow 客户端工具一起使用。 想获得 Elastic 认证吗&#xff1f;了解下一期 Elasticsearch Engineer 培训的时间吧&#xff01; Elasticsearch 拥有众多新功能&#xff0c;助你为自己…...

编程新知 2025/6/21 15:43:04

IT供电系统绝缘监测及故障定位解决方案

随着新能源的快速发展&#xff0c;光伏电站、储能系统及充电设备已广泛应用于现代能源网络。在光伏领域&#xff0c;IT供电系统凭借其持续供电性好、安全性高等优势成为光伏首选&#xff0c;但在长期运行中&#xff0c;例如老化、潮湿、隐裂、机械损伤等问题会影响光伏板绝缘层…...

编程新知 2025/6/24 3:19:42

什么？连接服务器也能可视化显示界面？：基于X11 Forwarding + CentOS + MobaXterm实战指南

文章目录 什么是X11？环境准备实战步骤1️⃣ 服务器端配置（CentOS）2️⃣ 客户端配置（MobaXterm）3️⃣ 验证X11 Forwarding4️⃣ 运行自定义GUI程序(Python示例)5️⃣ 成功效果![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/55aefaea8a9f477e86d065227851fe3d.pn…...

编程新知 2025/6/24 5:44:40

AI病理诊断七剑下天山，医疗未来触手可及

一、病理诊断困局&#xff1a;刀尖上的医学艺术 1.1 金标准背后的隐痛 病理诊断被誉为"诊断的诊断"&#xff0c;医生需通过显微镜观察组织切片&#xff0c;在细胞迷宫中捕捉癌变信号。某省病理质控报告显示&#xff0c;基层医院误诊率达12%-15%&#xff0c;专家会诊…...

编程新知 2025/6/21 11:59:00

JVM虚拟机：内存结构、垃圾回收、性能优化

1、JVM虚拟机的简介 Java 虚拟机（Java Virtual Machine 简称：JVM）是运行所有 Java 程序的抽象计算机，是 Java 语言的运行环境，实现了 Java 程序的跨平台特性。JVM 屏蔽了与具体操作系统平台相关的信息，使得 Java 程序只需生成在 JVM 上运行的目标代码（字节码），就可以…...

编程新知 2025/6/26 2:11:09

vulnyx Blogger writeup

信息收集 arp-scan nmap 获取userFlag 上web看看 一个默认的页面&#xff0c;gobuster扫一下目录 可以看到扫出的目录中得到了一个有价值的目录/wordpress&#xff0c;说明目标所使用的cms是wordpress&#xff0c;访问http://192.168.43.213/wordpress/然后查看源码能看到 这…...

编程新知 2025/6/22 13:19:47

【堆垛策略】设计方法

堆垛策略的设计是积木堆叠系统的核心&#xff0c;直接影响堆叠的稳定性、效率和容错能力。以下是分层次的堆垛策略设计方法&#xff0c;涵盖基础规则、优化算法和容错机制&#xff1a; 1. 基础堆垛规则 (1) 物理稳定性优先 重心原则&#xff1a; 大尺寸/重量积木在下&#xf…...

编程新知 2025/6/22 10:48:11

聚六亚甲基单胍盐酸盐市场深度解析：现状、挑战与机遇

根据 QYResearch 发布的市场报告显示&#xff0c;全球市场规模预计在 2031 年达到 9848 万美元&#xff0c;2025 - 2031 年期间年复合增长率&#xff08;CAGR&#xff09;为 3.7%。在竞争格局上&#xff0c;市场集中度较高&#xff0c;2024 年全球前十强厂商占据约 74.0% 的市场…...

编程新知 2025/6/21 12:45:55

rm视觉学习1-自瞄部分

首先先感谢中南大学的开源&#xff0c;提供了很全面的思路&#xff0c;减少了很多基础性的开发研究 我看的阅读的是中南大学FYT战队开源视觉代码 链接&#xff1a;https://github.com/CSU-FYT-Vision/FYT2024_vision.git 1.框架&#xff1a; 代码框架结构&#xff1a;readme有…...

编程新知 2025/6/21 23:55:35