MySQL 介绍

发布时间: 更新时间: 总字数:671 阅读时间:2m 作者: 分享 复制网址

MySQL 介绍

介绍

  • Navicat 可视化工具
  • mysql-workbench MySQL 官方的可视化开发和管理平台,ubuntu 安装命令
snap connect mysql-workbench-community
snap connect mysql-workbench-community:password-manager-service
snap connect mysql-workbench-community:ssh-keys
  • Liquibase 数据库版本管理工具
  • mysql 端口
    • 3306 MySQL Classic 协议
    • 33060 MySQL X 协议,由 mysqlx_port 配置
    • 33062 MySQL Classic 协议的管理端口,由 admin_port 配置,MySQL 8.0.14 起提供
  • MYSQL SQL审核工具

命令介绍

命令

docker run -it -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root mysql:5.7

rename 重命名表

# rename
rename table <old-table-name> to <new-table-name>;

# aalter
alter table <old-table-name> rename as <new-table-name>;

json_extract 处理 json 字段

  • MySQL 5.7+ 开始提供 json_extract 函数
  • 格式
    • $.字段名 可以用来查询对应的 value
    • json_extract('字段', '$.key') json 对象
    • json_extract('字段', '$[数组下标].key') json 数组
JSON_EXTRACT(json_doc, path[, path] …)
  • 示例
# 创建数据库
create database test;
use test

# 创建表
CREATE TABLE `json_table` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `value` json DEFAULT NULL COMMENT 'json value',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# 表结构
mysql> desc json_table;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| id    | bigint(20) | NO   | PRI | NULL    | auto_increment |
| value | json       | YES  |     | NULL    |                |
+-------+------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> show create table json_table;
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                 |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| json_table | CREATE TABLE `json_table` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `value` json DEFAULT NULL COMMENT 'json value',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

# 插入数据
insert into `json_table` values (1, '{"name": "xie", "age": 18}');
insert into `json_table` values (2, '{"name": "xianbin", "site": "https://www.xiexianbin.cn"}');

# 查询所有字段
mysql> select * from json_table;
+----+----------------------------------------------------------+
| id | value                                                    |
+----+----------------------------------------------------------+
|  1 | {"age": 18, "name": "xie"}                               |
|  2 | {"name": "xianbin", "site": "https://www.xiexianbin.cn"} |
+----+----------------------------------------------------------+
2 rows in set (0.00 sec)

# 简单示例
mysql> select json_extract('{"name":"xianbin","age":"18"}', "$.age");
+-------------------------------------------------------+
| json_extract('{"name":"xianbin","age":"18"}',"$.age") |
+-------------------------------------------------------+
| "18"                                                  |
+-------------------------------------------------------+
1 row in set (0.00 sec)

# 查询 json 的 name 字段
mysql> select json_extract(`value`, '$.name') from `json_table`;
+---------------------------------+
| json_extract(`value`, '$.name') |
+---------------------------------+
| "xie"                           |
| "xianbin"                       |
+---------------------------------+
2 rows in set (0.00 sec)

# 默认值为 NULL
mysql> select json_extract(`value`, '$.name') as `name`, json_extract(`value`, '$.site') as `site` from `json_table`;
+-----------+-----------------------------+
| name      | site                        |
+-----------+-----------------------------+
| "xie"     | NULL                        |
| "xianbin" | "https://www.xiexianbin.cn" |
+-----------+-----------------------------+
2 rows in set (0.00 sec)

# 数组示例
mysql> insert into `json_table` values (3, '[{"name": "xiexianbin", "site": "https://www.xiexianbin.cn"}]');

# 查询数据的内容
mysql> select json_extract(`value`, '$[0].name') from `json_table` where id = 3;
+------------------------------------+
| json_extract(`value`, '$[0].name') |
+------------------------------------+
| "xiexianbin"                       |
+------------------------------------+
1 row in set (0.00 sec)

# json 查询条件
mysql> select * from `json_table` where json_extract(`value`, '$.name') = 'xianbin';
+----+----------------------------------------------------------+
| id | value                                                    |
+----+----------------------------------------------------------+
|  2 | {"name": "xianbin", "site": "https://www.xiexianbin.cn"} |
+----+----------------------------------------------------------+
1 row in set (0.00 sec)

RETURNING

  • 生效版本
    • MySQL 5.7 20210330 及以上
    • MySQL 8.0 20220330 及以上
  • 使用
    • DELETE...RETURNING 语句返回前镜像数据
    • INSERT/REPLACE...RETURNING 返回后镜像数据
Home Archives Categories Tags Statistics
本文总阅读量 次 本站总访问量 次 本站总访客数