如何使用JS操作MySQL数据库

MySQL 从若干年前就开始布局文档存储 Document Store，越来越多的 JSON 特性被融入到 MySQL 中，尤其是 MySQL 8.0 之后的版本。

MySQL 提供了 MySQL Shell 工具，可以通过 X-DevAPI 直接使用 JavaScrpit 或者 Python 语言操控 MySQL 中的数据，从而简化开发。

7 月份，MySQL 9.0 发布，详情请参阅：

• 最新发布！MySQL 9.0的VECTOR文档更新

在 MySQL 9.0 企业版中增加了一项重磅特性，支持使用 JavaScript 语言编写存储过程，编写调试存储过程将更加便捷。

例如，

mysql> CREATE FUNCTION add_nos(arg1 INT, arg2 INT) 
    ->   RETURNS INT LANGUAGE JAVASCRIPT AS 
    ->   $$
    $>     return arg1 + arg2
    $>   $$
    ->   ;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT add_nos(12,52);
+----------------+
| add_nos(12,52) |
+----------------+
|             64 |
+----------------+
1 row in set (0.00 sec)

接下来，让我们先回忆下如何使用 JS 操作 MySQL。

使用MySQL客户端连接到数据库

常规操作，使用 MySQL 客户端连接数据库，创建表，写入数据，并查看表数据。

[shawnyan@rl8 ~]$ mysql -uroot mytest
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 92
Server version: 9.0.1 MySQL Community Server - GPL

Copyright (c) 2000, 2024, 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.

mysql> create table tbl (id int, name varchar(10));
Query OK, 0 rows affected (0.00 sec)

mysql> insert tbl select 1, 'Yan';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> table tbl;
+------+------+
| id   | name |
+------+------+
|    1 | Yan  |
+------+------+
1 row in set (0.00 sec)

使用MySQL Shell连接到数据库

对表进行操作

使用 MySQL Shell 连接到数据库，写入数据，并查看表数据。

[shawnyan@rl8 ~]$ mysqlsh root@localhost:33060 --js
MySQL Shell 9.0.1

Copyright (c) 2016, 2024, 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 '\?' for help; '\quit' to exit.
Creating a session to 'root@localhost:33060'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 96 (X protocol)
Server version: 9.0.1 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  localhost:33060+ ssl  JS > \use mytest
Default schema `mytest` accessible through db.
 MySQL  localhost:33060+ ssl  mytest  JS > db.getTables();
[
    <Table:tbl>
]
 MySQL  localhost:33060+ ssl  mytest  JS > db.tbl.select();
+----+------+
| id | name |
+----+------+
|  1 | Yan  |
+----+------+
1 row in set (0.0007 sec)
 MySQL  localhost:33060+ ssl  mytest  JS > db.tbl.insert("id", "name").values(2, 'Shawn');
Query OK, 1 item affected (0.0016 sec)
 MySQL  localhost:33060+ ssl  mytest  JS > db.tbl.select().where("id = 2");
+----+-------+
| id | name  |
+----+-------+
|  2 | Shawn |
+----+-------+
1 row in set (0.0005 sec)

对集合进行操作

创建、查看集合。

 MySQL  localhost:33060+ ssl  mytest  JS > db.createCollection('city');
<Collection:city>
 MySQL  localhost:33060+ ssl  mytest  JS > db.getCollections();
[
    <Collection:city>
]

写入、查看数据。

 MySQL  localhost:33060+ ssl  mytest  JS > db.city.add({"id": 1, "Name": "Yan"});
Query OK, 1 item affected (0.0018 sec)
 MySQL  localhost:33060+ ssl  mytest  JS > db.city.find("id = 1");
{
    "id": 1,
    "_id": "000066d6dd3d0000000000000002",
    "Name": "Yan"
}
1 document in set (0.0005 sec)

创建索引。

 MySQL  localhost:33060+ ssl  mytest  JS > db.city.createIndex("idx_city_id", {fields: [{field: '$.id', type: 'INTEGER'}]});
Query OK, 0 rows affected (0.0133 sec)

使用MySQL客户端连接到数据库

查看集合

回到刚才的 MySQL 客户端，查看集合的定义。

mysql> show create table city\G
*************************** 1. row ***************************
       Table: city
Create Table: CREATE TABLE `city` (
  `doc` json DEFAULT NULL,
  `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
  `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
  `$ix_i_C6EF652A87142734264FBA60F41C59108CF4D8CA` int GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.id')) VIRTUAL,
  PRIMARY KEY (`_id`),
  KEY `idx_city_id` (`$ix_i_C6EF652A87142734264FBA60F41C59108CF4D8CA`),
  CONSTRAINT `$val_strict_E0FADE6FD008F295573451C644C5CA04336E7955` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

mysql> table city\G
*************************** 1. row ***************************
                                           doc: {"id": 1, "_id": "000066d6dd3d0000000000000002", "Name": "Yan"}
                                           _id: 0x30303030363664366464336430303030303030303030303030303032
                                  _json_schema: {"type": "object"}
$ix_i_C6EF652A87142734264FBA60F41C59108CF4D8CA: 1
1 row in set (0.00 sec)

修改集合

在 MySQL 客户端中修改集合数据。

mysql> select doc from city where doc->>"$.id" = 1;
+-----------------------------------------------------------------+
| doc                                                             |
+-----------------------------------------------------------------+
| {"id": 1, "_id": "000066d6dd3d0000000000000002", "Name": "Yan"} |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> update city set doc = json_replace(doc, '$."Name"', 'Yan1') where doc->>"$.id" = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select json_extract(doc, '$.id') as id, json_extract(doc, '$.Name') as name from city;
+------+--------+
| id   | name   |
+------+--------+
| 1    | "Yan1" |
+------+--------+
1 row in set (0.00 sec)

請注意，除了新增文件時指定的字段外，還有一個字段，即_id。每個文檔都需要一個名為 _id 的標識符欄位。 _id 欄位的值在同一集合中的所有文件中必須是唯一的。文檔 ID 由伺服器而非客戶端生成，因此 MySQL Shell 不會自動設定 _id 值。如果文檔不包含 _id 字段，MySQL 將設定 _id 值。

在 MySQL 客户端中增添集合数据。

mysql> SELECT JSON_OBJECT('id', 2, 'Name', 'Shawn', '_id', '000066d6dd3d0000000000000003') into @j;
Query OK, 1 row affected (0.00 sec)

mysql> insert city (doc) values (@j);
Query OK, 1 row affected (0.00 sec)

mysql> table city\G
*************************** 1. row ***************************
                                           doc: {"id": 1, "_id": "000066d6dd3d0000000000000002", "Name": "Yan1"}
                                           _id: 0x30303030363664366464336430303030303030303030303030303032
                                  _json_schema: {"type": "object"}
$ix_i_C6EF652A87142734264FBA60F41C59108CF4D8CA: 1
*************************** 2. row ***************************
                                           doc: {"id": 2, "_id": "000066d6dd3d0000000000000003", "Name": "Shawn"}
                                           _id: 0x30303030363664366464336430303030303030303030303030303033
                                  _json_schema: {"type": "object"}
$ix_i_C6EF652A87142734264FBA60F41C59108CF4D8CA: 2
2 rows in set (0.00 sec)

本节内容就先介绍到这里。