json存入mysql的一种设计方案

原创已于 2024-12-02 19:47:20 修改 · 139 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java #数据库 #服务器

于 2024-12-01 23:09:36 首次发布

{
  "user_id": 1,
  "name": "Alice",
  "email": "alice@example.com",
  "address": {
    "street": "123 Main St",
    "city": "Wonderland",
    "zip": "12345"
  },
  "orders": [
    {
      "order_id": 101,
      "product": "Book",
      "quantity": 2
    },
    {
      "order_id": 102,
      "product": "Pen",
      "quantity": 5
    }
  ]
}

CREATE TABLE users{
	_id INT PRIMARY KEY AUTO_INCREMENT,
	user_id INT,
	name VARCHAR(100),
	email VARCHAR(100)
}

CREATE TABEL address{
	_id INT PRIMARY KEY AUTO_INCREMENT,
	street VARCHAR(100),
	city VARCHAR(100),
	zip VARCHAR(100),
	_parentTable_users_user_id INT,
	FOREIGN KEY (_parentTable_users_user_id) REFERENCES users(user_id)
}

CREATE TABLE orders{
	_id INT PRIMARY KEY AUTO_INCREMENT,
	order_id INT,
	product INT,
	quantity INT,
	_parentTable_users_user_id INT,
	FOREIGN KEY(_parentTable_users_user_id) REFERENCES users(user_id)
}

-- 插入用户
INSERT INTO users (user_id, name, email) VALUES (1, 'Alice', 'alice@example.com');

-- 插入地址
INSERT INTO addresses (_parentTable_users_user_id, street, city, zip) VALUES (1, '123 Main St', 'Wonderland', '12345');

-- 插入订单
INSERT INTO orders (order_id, _parentTable_users_user_id, product, quantity) VALUES (101, 1, 'Book', 2);
INSERT INTO orders (order_id, _parentTable_users_user_id, product, quantity) VALUES (102, 1, 'Pen', 5);

SELECT 
	u.user_id
    u.name, 
    u.email, 
    a.street, 
    a.city, 
    a.zip, 
    o.product, 
    o.quantity
FROM 
    users u
JOIN 
    addresses a ON u.user_id = a._parentTable_users_user_id
JOIN 
    orders o ON u.user_id = o._parentTable_users_user_id
WHERE 
    u.user_id = 1;