当前位置：首页 > news >正文

MySQL JSON 字段使用（创建表 + 插入 + 查询 + Java 代码实战）

news 2026/8/3 17:43:57

MySQL JSON 字段使用（创建表 + 插入 + 查询 + Java 代码实战）

一、MySQL 创建带 JSON 字段的表（带主键）

JSON 是 MySQL 5.7+ 原生类型，支持格式校验、高效查询、局部修改。

-- 创建表：主键 + JSON 字段 CREATE TABLE USERS_JSON ( ORDER_ID INT PRIMARY KEY NOT NULL AUTO_INCREMENT, -- 自增主键 ORDERS JSON DEFAULT NULL -- JSON 类型字段 );

JSON：专用 JSON 类型，不是普通文本
必须合法 JSON 才能插入，格式错误直接报错

CREATE TABLE users_json ( id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, orders JSON DEFAULT NULL )

二、MySQL SQL 语句：插入 JSON 数据

-- 插入 JSON 数据 INSERT INTO USERS_JSON (ORDERS) VALUES ('{ "applicant": { "aoeAes": "吴秀梅", "aoeSm4": "Beijing Refining Network Technology Co.Ltd.", "aoeSm4A": "软件测试工程师", "aoeEmail": "qianxiulan@yahoo.com", "aoePhone": "15652996964", "aoeIdCard": "210302199608124861", "aoeOfficerCard": "武水电字第3632734号", "aoePassport": "BWP018930705", "aoeGeneralIdCard": "0299233902", "aoeCreditCard": "6212262502009182455", "aoeJob": "软件测试工程师" } }'); -- 插入 JSON 数据 INSERT INTO users_json (orders) VALUES ('{ "applicant": { "aoeAes": "吴秀梅", "aoeSm4": "Beijing Refining Network Technology Co.Ltd.", "aoeSm4A": "软件测试工程师", "aoeEmail": "qianxiulan@yahoo.com", "aoePhone": "15652996964", "aoeIdCard": "210302199608124861", "aoeOfficerCard": "武水电字第3632734号", "aoePassport": "BWP018930705", "aoeGeneralIdCard": "0299233902", "aoeCreditCard": "6212262502009182455", "aoeJob": "软件测试工程师" } }');

三、MySQL SQL 查询 JSON 字段内容

1. 查询整个 JSON

SELECT * FROM USERS_JSON;
SELECT * FROM users_json;

2. 精准提取 JSON 内部字段

-- 提取姓名、电话、邮箱 SELECT ORDER_ID, ORDERS->'$.applicant.aoeAes' AS 姓名, ORDERS->'$.applicant.aoePhone' AS 手机号, ORDERS->'$.applicant.aoeEmail' AS 邮箱 FROM USERS_JSON; -- 提取姓名、电话、邮箱 SELECT id, orders->'$.applicant.aoeAes' AS 姓名, orders->'$.applicant.aoePhone' AS 手机号, orders->'$.applicant.aoeEmail' AS 邮箱 FROM users_json;

3. 去掉查询结果的双引号

SELECT ORDER_ID, ORDERS->>'$.applicant.aoeAes' AS 姓名 FROM USERS_JSON; SELECT id, orders->>'$.applicant.aoeAes' AS 姓名 FROM users_json;

四、Java 代码插入数据到 MySQL JSON 字段（最稳定写法）

核心规则

JSON 内部双引号"在 Java 中必须转义为\"
SQL 中 JSON 整体用单引号'包裹

// 1. 定义 JSON 字符串（双引号全部转义） String json = "{\"applicant\":{\"aoeAes\":\"吴秀梅\",\"aoeSm4\":\"Beijing Refining Network Technology Co.Ltd.\",\"aoeSm4A\":\"软件测试工程师\",\"aoeEmail\":\"qianxiulan@yahoo.com\",\"aoePhone\":\"15652996964\",\"aoeIdCard\":\"210302199608124861\",\"aoeOfficerCard\":\"武水电字第3632734号\",\"aoePassport\":\"BWP018930705\",\"aoeGeneralIdCard\":\"0299233902\",\"aoeCreditCard\":\"6212262502009182455\",\"aoeJob\":\"软件测试工程师\"}}"; // 2. 拼接 SQL（外层单引号包裹 JSON） String sql = "INSERT INTO USERS_JSON (ORDERS) VALUES ('" + json + "')"; // 3. 执行插入（你自己的 DB 执行工具类） c.DBExecute(sql);

五、Java 代码查询 MySQL JSON 字段

String sql = "SELECT ORDER_ID, ORDERS->>'$.applicant.aoeAes' AS name FROM USERS_JSON"; // 执行查询，获取结果集 ResultSet rs = c.DBQuery(sql); while (rs.next()) { int orderId = rs.getInt("ORDER_ID"); String name = rs.getString("name"); System.out.println("ID：" + orderId + "，姓名：" + name); } String sql = "SELECT id, orders->>'$.applicant.aoeAes' AS name FROM users_json";

六、JSON 字段三大优势

强制格式校验：不是合法 JSON 无法插入
高效查询：直接提取键值，无需全文检索
局部修改：可单独更新 JSON 内部字段

七、常见错误总结

JSON 用单引号：{'key':'value'}❌
Java 未转义双引号："{"key":"value"}"❌
SQL 未用单引号包裹 JSON❌

文章总结

建表：字段名 JSON
插入：SQL 用单引号，Java 双引号转义\"
查询：字段->'$.key'
Java 插入 / 查询：本文代码直接复制使用

有虚字段的json表

CREATE TABLE `u_user` ( `user_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0', `user_name` varchar(655) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, `user_alias` varchar(655) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `user_desc` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `user_password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `status` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `user_extends` json DEFAULT NULL, `created_date` datetime DEFAULT NULL, `updated_date` datetime DEFAULT NULL, `user_idNumber` varchar(655) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci GENERATED ALWAYS AS (json_unquote(json_extract(`user_extends`,_utf8mb4'$.idNumber'))) VIRTUAL, `work_wechat_user_id` varchar(455) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci GENERATED ALWAYS AS (json_unquote(json_extract(`user_extends`,_utf8mb4'$.workWechatUserId'))) VIRTUAL, `version` int DEFAULT '0', `area_enabled` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '0', `phone` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `openid` varchar(455) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `login_date` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`user_id`) USING BTREE, KEY `idx_user_name` (`user_name`) USING BTREE, KEY `idx_user_alias` (`user_alias`) USING BTREE, KEY `updated_date` (`updated_date`), KEY `idx_created` (`created_date`), KEY `phone` (`phone`), KEY `idx_user_idNumber` (`user_idNumber`) USING BTREE, KEY `work_wechat_user_id` (`work_wechat_user_id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC; INSERT INTO u_user ( user_id, user_name, user_desc, user_password, user_extends, created_date, updated_date ) VALUES ( 'user001', '张三', '市场部-产品经理', '123456', '{"idNumber":"110101199001011234","workWechatUserId":"ww_zhang123"}', NOW(), NOW() ); select user_id,user_extends from u_user

查看全文

http://www.cnnetsun.cn/news/1761288.html