如何在 Node.js、PHP、Python 和 Java 中集成 NoSQL 与 SQL (MySQL 8.0)

如何在 Node.js、PHP、Python 和 Java 中集成 NoSQL 与 SQL (MySQL 8.0)

使用 MySQL 8.0 文档存储功能

MySQL 8.0 提供了对 JSON 数据类型的原生支持以及通过 X DevAPI 对文档存储的支持。这意味着可以将 MySQL 当作文档数据库来使用，类似于 MongoDB。

Node.js 集成示例

为了在 Node.js 应用程序中同时操作传统表格数据和 JSON 文档，在建立连接时需指定 session 参数并启用 devapi：

const mysqlx = require('@mysql/xdevapi');

let session = await mysqlx.getSession({
    host: 'localhost',
    port: 33060,
    user: 'root',
    password: 'password'
});

// 创建或获取一个名为 mySchema 的 schema（相当于库）
let schema = await session.getSchema('mydb2001');
if (!schema.existsInDatabase()) {
    schema = await session.createSchema('mydb2001');
}

// 获取集合对象
let collection = await schema.getCollection('documents');
if (!collection.existsInDatabase()) {
    collection = await schema.createCollection('documents');
}

此代码片段展示了如何设置会话并通过 X DevAPI 访问基于 JSON 的集合。

PHP 集成示例

对于 PHP 来说，官方提供了 MySQLi 和 PDO_MySQL 扩展用于常规查询；而对于文档存储，则推荐使用 MySQL Shell 或者第三方库如 php-mysqlx:

<?php
$dsn = "mysqlx://root:@localhost/mydb2001?ssl-mode=disabled";
try {
    $pdo = new \PDO($dsn);
    
    // 插入JSON文档到集合中
    $stmt = $pdo->prepare("INSERT INTO documents VALUES (:doc)");
    $stmt->execute([':doc' => json_encode([
        '_id' => uniqid(),
        'title' => 'Example Document',
        'content' => 'This is an example document stored as a JSON object.'
    ])]);
} catch (\PDOException $e) {
    echo "Error!: " . $e->getMessage();
}
?>

这段脚本说明了怎样向名为 documents 的集合插入一个新的 JSON 文档。

Python 集成示例

Python 可以借助 mysql-connector-python 实现对 MySQL Server 的访问，并且能够方便地调用 X DevAPI 方法来进行文档存储的操作：

import mysql.connector
from mysql.xdevapi import Session, Schema, Collection

connection_params = {
    "host": "localhost",
    "port": 33060,
    "user": "root",
    "password": "password"
}

session = Session(**connection_params)
schema = session.get_schema("mydb2001")

# 如果不存在则创建新的集合
if not schema.has_collection("documents"):
    collection = schema.create_collection("documents")
else:
    collection = schema.get_collection("documents")

new_doc = {"_id": "abc", "name": "John Doe"}
result = collection.add(new_doc).execute()
print(f"{result.get_affected_items_count()} item(s) inserted.")

上述例子解释了如何利用 Python 脚本来管理 MySQL 内部的 JSON 文档集合作业。

Java 集成示例

Java 开发人员可以通过 JDBC API 连接到 MySQL 并执行标准 SQL 查询。而针对文档存储特性，建议采用 MySQL Connector/J 版本 8.0+ 支持的新特性——X DevAPI 接口：

import com.mysql.cj.xdevapi.*;

public class Main {
    public static void main(String[] args) throws Exception {
        String connectionString = "jdbc:mysql://localhost:33060/?useSSL=false&serverTimezone=UTC";

        try (Session sess = SessionFactory.getInstance().create(connectionString)) {
            Schema db = sess.getDefaultSchema();

            // Create or get the existing collection named 'documents'.
            Collection col;
            if (!db.existsInDatabase()) {
                col = db.createCollection("documents");
            } else {
                col = db.getCollection("documents");
            }

            AddStatement addStmt = col.add(
                    "{\"_id\": \"def\", \"name\": \"Jane Smith\"}"
            );
            Result res = addStmt.execute();
            System.out.println(res.getAffectedItemsCount() + " row(s) added.");
        }
    }
}

该段源码体现了 Java 程序员应如何运用最新版驱动器完成对 MySQL 8.0 上 JSON 文档集合的基本 CRUD 操作。
This year’s Oracle Code One offers a full track for MySQL, so join us to discover why NoSQL + SQL = MySQL and hear why the world’s most popular open source database has become even more powerful. The new NoSQL feature in MySQL is dramatically changing data management for the better. Developers can now use the same database for both SQL and NoSQL, making data management a lot easier.

We also built a full solution to deliver high availability out-of-the-box and very easy to deploy and maintain. MySQL InnoDB Cluster is also DevOps friendly.

And finally, join us to discover the new MySQL client made for developers: the MySQL Shell. Speakers include MySQL engineers, domain experts, industry gurus, and engineers from companies such as Facebook, Zuora, Mercari, WePay, Credorax, and more. Learn why data management is critical to their business. Hear from MySQL engineers how to take full advantage of MySQL new features with NodeJS, PHP, Java, Python, and more. Gain insight from the leading web companies, hear their success stories, and get tips and techniques for how you can improve database performance, security, compliance, and high availability.

This year’s sessions include the following:

MySQL 8.0 at Facebook
Managing MySQL at Scale in Facebook
Mercari Meets MySQL Analytics Service

Browse MySQL sessions
About Oracle Code One
Oracle Developers
Other Conferences
Oracle Code
Oracle OpenWorld
#CodeOne
Twitter
Facebook
YouTube
Oracle
Legal Notices
Terms of Use
Privacy