MySQL JSON 操作

小蓝 • 2024-12-15 12:41 • 编程

一、MySQL 操作题

MySQL 是一个流行的关系型数据库管理系统，用于存储和管理数据。在进行 MySQL JSON 操作之前，需要先了解 MySQL 基础操作。

1.创建数据库


CREATE DATABASE test_db;

2.选择数据库


USE test_db;

3.创建表


CREATE TABLE users(
    id INT NOT NULL,
    name VARCHAR(30) NOT NULL,
    age INT NOT NULL,
    PRIMARY KEY (id)
);

4.插入数据


INSERT INTO users (id, name, age) VALUES (1, 'Lucy', 20);
INSERT INTO users (id, name, age) VALUES (2, 'Lily', 22);
INSERT INTO users (id, name, age) VALUES (3, 'Tom', 25);

二、MySQL 的 JSON 操作

MySQL 5.7 及以上版本支持 JSON 类型，可以将 JSON 数据存储在 MySQL 数据库中，并且可以对 JSON 数据进行操作。

1.创建 JSON 字段


CREATE TABLE products(
    id INT NOT NULL,
    name VARCHAR(50) NOT NULL,
    detail JSON,
    PRIMARY KEY (id)
);

2.插入 JSON 数据


INSERT INTO products (id, name, detail) VALUES (1, 'computer', '{"brand":"Apple","color":"silver"}');
INSERT INTO products (id, name, detail) VALUES (2, 'car', '{"brand":"BMW","color":"white"}');
INSERT INTO products (id, name, detail) VALUES (3, 'phone', '{"brand":"Samsung","color":"black"}');

3.查询 JSON 数据


SELECT * FROM products WHERE detail->>'$.brand' = 'Apple';

4.更新 JSON 数据


UPDATE products SET detail = JSON_REPLACE(detail, '$.color', 'rose gold') WHERE id = 1;

5.删除 JSON 数据


UPDATE products SET detail = JSON_REMOVE(detail, '$.brand') WHERE id = 3;

三、MySQL 操作

MySQL 还提供了其他重要的操作，如数据导入导出和指定行、列的筛选。

1.导出数据为 CSV 文件


SELECT *
FROM products
INTO OUTFILE '/path/to/file/products.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

2.导入 CSV 文件到数据库


LOAD DATA INFILE '/path/to/file/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

3.指定行、列筛选


SELECT name, age FROM users WHERE age > 21;

四、MySQL 操作 Excel

MySQL 还支持将 Excel 文件中的数据导入到数据库，并且可以进行 SQL 查询。

1.创建新表用于存储 Excel 数据


CREATE TABLE excel_data(
    name VARCHAR(50) NOT NULL,
    age INT,
    address VARCHAR(255)
);

2.导入 Excel 数据到数据库


LOAD DATA INFILE '/path/to/file/excel_data.xls'
IGNORE INTO TABLE excel_data
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS;

3.查询 Excel 数据


SELECT name, age FROM excel_data WHERE age > 21;

五、MySQL JSON 字段坏处

MySQL JSON 字段虽然提供了操作 JSON 数据的便利性，但也存在一些坏处。

1.不支持索引

由于 JSON 数据的不规则性，MySQL 不支持在 JSON 字段上创建索引。这可能导致查询效率降低。

2.难以调试

在 JSON 字段上进行复杂查询时，很难调试 SQL 语句，尤其是在 JSON 数据存在多层嵌套时。

3.存储空间

JSON 数据通常比字符串类型数据占用更多的存储空间，这会增加数据库的存储开销。

以上是对 MySQL JSON 操作的详细阐述，其中涉及基础操作、JSON 操作、数据导入导出和 Excel 操作等多个方面。

原创文章，作者：小蓝，如若转载，请注明出处：https://www.506064.com/n/256495.html