使用 MariaDB 和 MySQL 的表格

MariaDB 是一个与 MySQL 兼容的开源对象关系数据库系统。

¥MariaDB is an open source object-relational database system compatible with MySQL.

SheetJS 是一个用于从电子表格读取和写入数据的 JavaScript 库。

¥SheetJS is a JavaScript library for reading and writing data from spreadsheets.

该演示使用 SheetJS 在电子表格和 MariaDB 数据库之间交换数据。我们将探讨如何将数据库中的表保存到电子表格以及如何将电子表格中的数据添加到数据库中。

¥This demo uses SheetJS to exchange data between spreadsheets and MariaDB databases. We'll explore how to save tables from a database to spreadsheets and how to add data from spreadsheets into a database.

强烈建议将 MariaDB 与查询构建器或 ORM 一起使用。

¥It is strongly recommended to use MariaDB with a query builder or ORM.

虽然可以直接生成 SQL 语句，但存在许多微妙的细节和陷阱。经过实际检验的解决方案通常可以针对 SQL 注入和其他漏洞提供缓解措施。

¥While it is possible to generate SQL statements directly, there are many subtle details and pitfalls. Battle-tested solutions generally provide mitigations against SQL injection and other vulnerabilities.

测试部署

本 demo 在以下环境下进行了测试：

¥This demo was tested in the following environments:

MariaDB	连接器库	日期
11.3.2	mysql2(3.9.7)	2024-05-04

集成详情

¥Integration Details

SheetJS NodeJS 模块 可以加载到连接到 MariaDB 和 MySQL 数据库的 NodeJS 脚本中。

¥The SheetJS NodeJS module can be loaded in NodeJS scripts that connect to MariaDB and MySQL databases.

该演示使用 mysql2 连接器模块 [^1]，但相同的机制适用于其他 MariaDB 和 MySQL 库。

¥This demo uses the mysql2 connector module[^1], but the same mechanics apply to other MariaDB and MySQL libraries.

导出数据

¥Exporting Data

Connection#execute 返回一个解析为结果数组的 Promise。结果的第一个条目是对象数组。

¥Connection#execute returns a Promise that resolves to a result array. The first entry of the result is an array of objects.

SheetJS json_to_sheet 方法 [^2] 可以从对象数组生成工作表对象 [^3]：

¥The SheetJS json_to_sheet method[^2] can generate a worksheet object[^3] from the array of objects:

const mysql = require("mysql2/promise"), XLSX = require("xlsx");
const conn = await mysql.createConnection({
  database: "SheetJSMariaDB",
  /* ... other options ... */
});

const table_name = "Tabeller1"; // name of table

/* fetch all data from specified table */
const [rows, fields] = await conn.execute(`SELECT * FROM ${mysql.escapeId(table_name)}`);

/* generate a SheetJS worksheet object from the data */
const worksheet = XLSX.utils.json_to_sheet(rows);

可以使用实用函数 [^4] 从工作表构建工作簿对象。可以使用 SheetJS writeFile 方法 [^5] 导出工作簿：

¥A workbook object can be built from the worksheet using utility functions[^4]. The workbook can be exported using the SheetJS writeFile method[^5]:

/* create a new workbook and add the worksheet */
const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, worksheet, "Sheet1");

/* export workbook to XLSX */
XLSX.writeFile(wb, "SheetJSMariaDBExport.xlsx");

导入数据

¥Importing Data

SheetJS sheet_to_json 函数 [^6] 接受一个工作表对象并生成一个对象数组。

¥The SheetJS sheet_to_json function[^6] takes a worksheet object and generates an array of objects.

必须从对象手动生成查询。假设对象中的字段名称与列标题匹配，则循环可以生成 INSERT 个查询。

¥Queries must be manually generated from the objects. Assuming the field names in the object match the column headers, a loop can generate INSERT queries.

MariaDB 不允许使用可变列名的参数化查询

¥MariaDB does not allow parameterized queries with variable column names

INSERT INTO table_name (?) VALUES (?);
-- ---------------------^ variable column names are not valid

查询是手动生成的。为了帮助防止 SQL 注入漏洞，未记录的 escapeId 方法 [^7] 对标识符和字段进行转义。

¥Queries are generated manually. To help prevent SQL injection vulnerabilities, the undocumented escapeId method [^7] escapes identifiers and fields.

/* generate an array of arrays from the worksheet */
const aoo = XLSX.utils.sheet_to_json(ws);

const table_name = "Blatte1"; // name of table

/* loop through the data rows */
for(let row of aoo) {
  /* generate INSERT column names and values */
  const ent = Object.entries(row);
  const Istr = ent.map(e => I(e[0])).join(", ");
  const Vstr = ent.map(e => E(e[1])).join(", ");

  /* execute INSERT statement */
  await conn.execute(`INSERT INTO ${I(table_name)} (${Istr}) VALUES (${Vstr})`);
}

创建表

¥Creating a Table

可以扫描对象数组以确定列名称和类型。有了名称和类型，就可以编写 CREATE TABLE 查询。

¥The array of objects can be scanned to determine column names and types. With the names and types, a CREATE TABLE query can be written.

Implementation Details (click to show)

The aoo_to_mariadb_table function:

• scans each row object to determine column names and types
• drops and creates a new table with the determined column names and types
• loads the entire dataset into the new table

/* create table and load data given an array of objects and a mysql2 connection */
async function aoo_to_mariadb_table(conn, aoo, table_name) {
  /* define types that can be converted (e.g. boolean can be stored in float) */
  const T_FLOAT = ["DOUBLE", "BOOLEAN"];
  const T_BOOL =  ["BOOLEAN"];

  /* types is a map from column headers to Knex schema column type */
  const types = {};

  /* names is an ordered list of the column header names */
  const names = [];

  /* loop across each row object */
  aoo.forEach(row =>
    /* Object.entries returns a row of [key, value] pairs */
    Object.entries(row).forEach(([k,v]) => {

      /* If this is first occurrence, mark unknown and append header to names */
      if(!types[k]) { types[k] = ""; names.push(k); }

      /* skip null and undefined values */
      if(v == null) return;

      /* check and resolve type */
      switch(typeof v) {
        /* change type if it is empty or can be stored in a float */
        case "number": if(!types[k] || T_FLOAT.includes(types[k])) types[k] = "DOUBLE"; break;
        /* change type if it is empty or can be stored in a boolean */
        case "boolean": if(!types[k] || T_BOOL.includes(types[k])) types[k] = "BOOLEAN"; break;
        /* no other type can hold strings */
        case "string": types[k] = "TEXT"; break;
        default: types[k] = "TEXT"; break;
      }
    })
  );

  const I = (id) => mysql.escapeId(id), E = (d) => mysql.escape(d);

  /* Delete table if it exists in the DB */
  await conn.execute(`DROP TABLE IF EXISTS ${I(table_name)};`);

  /* Create table */
  {
    const Istr = Object.entries(types).map(e => `${I(e[0])} ${e[1]}`).join(", ");
    await conn.execute(`CREATE TABLE ${I(table_name)} (${Istr});`);
  }

  /* Insert each row */
  for(let row of aoo) {
    const ent = Object.entries(row);
    const Istr = ent.map(e => I(e[0])).join(", ");
    const Vstr = ent.map(e => E(e[1])).join(", ");
    await conn.execute(`INSERT INTO ${I(table_name)} (${Istr}) VALUES (${Vstr})`);
  }

  return conn;
}

完整示例

¥Complete Example

0. 安装并启动 MariaDB 服务器。

   ¥Install and start the MariaDB server.

Installation Notes (click to show)

On macOS, install the mariadb formula with Homebrew:

brew install mariadb

The last few lines of the installer explain how to start the database:

Or, if you don't want/need a background service you can just run:
  /usr/local/opt/mariadb/bin/mysqld_safe --datadir\=/usr/local/var/mysql

Run the command to start a local database instance.

1. 删除任何名为 SheetJSMariaDB 的现有数据库：

   ¥Drop any existing database with the name SheetJSMariaDB:

mysql -e 'drop database if exists SheetJSMariaDB;'

如果服务器在其他地方运行，或者用户名与当前用户不同，则命令行标志可以覆盖默认值。

¥If the server is running elsewhere, or if the username is different from the current user, command-line flags can override the defaults.

选项	解释
-h HOSTNAME	服务器名称
-P PORT	指定端口号
-U USERNAME	指定用户名
-p PASSWORD	指定密码

2. 创建一个空的 SheetJSMariaDB 数据库：

   ¥Create an empty SheetJSMariaDB database:

mysql -e 'create database SheetJSMariaDB;'

连接器测试

¥Connector Test

3. 创建项目文件夹：

   ¥Create a project folder:

mkdir sheetjs-mariadb
cd sheetjs-mariadb
npm init -y

4. 安装 mysql2 连接器模块：

   ¥Install the mysql2 connector module:

npm i --save mysql2@3.6.5

5. 将以下示例代码块保存到 MariaDBTest.js：

   ¥Save the following example codeblock to MariaDBTest.js:

MariaDBTest.js

const mysql = require("mysql2/promise");
(async() => {

const conn = await mysql.createConnection({
  database:"SheetJSMariaDB",
  host: "127.0.0.1", // localhost
  port: 3306,
  user: "sheetjs",
  //password: ""
});

const [rows, fields] = await conn.execute('SELECT ? as message', ['Hello world!']);
console.log(rows[0].message); // Hello world!
await conn.end();

})();

6. 编辑新的 MariaDBTest.js 脚本并修改代码块中高亮的行以反映数据库部署设置。

   ¥Edit the new MariaDBTest.js script and modify the highlighted lines from the codeblock to reflect the database deployment settings.

• 将 user 设置为用户名（几乎可以肯定不是 "sheetjs"）

  ¥Set user to the username (it is almost certainly not "sheetjs")

• 如果服务器未在你的计算机上运行，请将 host 和 port 设置为正确的主机名和端口号。

  ¥If the server is not running on your computer, set host and port to the correct host name and port number.

• 如果服务器需要密码，请取消注释 password 行并将该值替换为密码。

  ¥If the server expects a password, uncomment the password line and replace the value with the password.

7. 运行脚本：

   ¥Run the script:

node MariaDBTest.js

它应该打印 Hello world!

¥It should print Hello world!

如果输出不是 Hello world! 或者存在错误，请将问题报告给 mysql2 连接器项目以进行进一步诊断。

¥If the output is not Hello world! or if there is an error, please report the issue to the mysql2 connector project for further diagnosis.

添加 SheetJS

¥Add SheetJS

8. 安装依赖：

   ¥Install dependencies:

npm i --save https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz

9. 下载 SheetJSMariaDB.js：

   ¥Download SheetJSMariaDB.js:

curl -LO https://xlsx.nodejs.cn/mariadb/SheetJSMariaDB.js

该脚本将：

¥This script will:

• 读取并解析测试文件 pres.numbers

  ¥read and parse the test file pres.numbers

• 创建与本地 MariaDB 服务器上的 SheetJSMariaDB 数据库的连接

  ¥create a connection to the SheetJSMariaDB database on a local MariaDB server

• 将第一个工作表中的数据加载到名为 Presidents 的表中

  ¥load data from the first worksheet into a table with name Presidents

• 断开并重新连接到数据库

  ¥disconnect and reconnect to the database

• 从表 Presidents 转储数据

  ¥dump data from the table Presidents

• 将数据集导出到 SheetJSMariaDB.xlsx

  ¥export the dataset to SheetJSMariaDB.xlsx

10. 编辑 SheetJSMariaDB.js 脚本。

    ¥Edit the SheetJSMariaDB.js script.

该脚本定义了一个 opts 对象：

¥The script defines an opts object:

SheetJSMariaDB.js (configuration lines)

const XLSX = require("xlsx");
const opts = {
  database:"SheetJSMariaDB",
  host: "127.0.0.1", // localhost
  port: 3306,
  user: "sheetjs",
  //password: ""
};

修改高亮的行以反映数据库部署设置。

¥Modify the highlighted lines to reflect the database deployment settings.

• 将 user 设置为用户名（几乎可以肯定不是 "sheetjs"）

  ¥Set user to the username (it is almost certainly not "sheetjs")

• 如果服务器未在你的计算机上运行，请将 host 和 port 设置为正确的主机名和端口号。

  ¥If the server is not running on your computer, set host and port to the correct host name and port number.

• 如果服务器需要密码，请取消注释 password 行并将该值替换为密码。

  ¥If the server expects a password, uncomment the password line and replace the value with the password.

11. 获取示例文件 pres.numbers：

    ¥Fetch the example file pres.numbers:

curl -L -O https://xlsx.nodejs.cn/pres.numbers

12. 运行脚本：

    ¥Run the script:

node SheetJSMariaDB.js

13. 验证结果：

    ¥Verify the result:

• SheetJSMariaDBExport.xlsx 可以在电子表格应用中打开或在终端中测试

  ¥SheetJSMariaDBExport.xlsx can be opened in a spreadsheet app or tested in the terminal

npx xlsx-cli SheetJSMariaDBExport.xlsx

• 可以使用 mysql 命令行工具查询数据库服务器。

  ¥The database server can be queried using the mysql command line tool.

如果服务器在本地运行，则命令将为：

¥If the server is running locally, the command will be:

mysql -D SheetJSMariaDB -e 'SELECT * FROM `Presidents`;'

输出应与下表一致：

¥The output should be consistent with the following table:

+--------------+-------+
| Name         | Index |
+--------------+-------+
| Bill Clinton |    42 |
| GeorgeW Bush |    43 |
| Barack Obama |    44 |
| Donald Trump |    45 |
| Joseph Biden |    46 |
+--------------+-------+

[^1]: 请参阅 mysql2 官方网站 了解更多信息。

¥See the official mysql2 website for more info.

[^2]: 见 json_to_sheet 于 "实用工具"

¥See json_to_sheet in "Utilities"

[^3]: 详细信息请参见 "SheetJS 数据模型" 中的 "Sheet 对象"。

¥See "Sheet Objects" in "SheetJS Data Model" for more details.

[^4]: 有关 book_new 和 book_append_sheet 的详细信息，请参阅 "工作簿助手" 于 "实用工具"。

¥See "Workbook Helpers" in "Utilities" for details on book_new and book_append_sheet.

[^5]: 见 writeFile 于 "写入文件"

¥See writeFile in "Writing Files"

[^6]: 见 sheet_to_json 于 "实用工具"

¥See sheet_to_json in "Utilities"

[^7]: 文档中未提及 mysql2 连接器库 escapeId 方法，但存在于 TypeScript 定义中。

¥The mysql2 connector library escapeId method is not mentioned in the documentation but is present in the TypeScript definitions.