JSON 转 SQL 实战案例 - 数据库批量导入 5 大场景（2026）

1
[
2
  {
3
    "username": "zhangsan",
4
    "email": "zhangsan@example.com",
5
    "password_hash": "$2a$10$...",
6
    "phone": "138****1234",
7
    "created_at": "2026-01-01 00:00:00",
8
    "status": "active"
9
  }
10
]

1
-- 批量插入（每 1000 条）
2
INSERT INTO users (username, email, password_hash, phone, created_at, status) VALUES
3
('zhangsan', 'zhangsan@example.com', '$2a$10$...', '138****1234', '2026-01-01 00:00:00', 'active'),
4
('lisi', 'lisi@example.com', '$2a$10$...', '139****5678', '2026-01-01 00:00:00', 'active');

1
-- 禁用索引
2
ALTER TABLE users DISABLE KEYS;
3

4
-- 批量插入
5
-- ... INSERT 语句 ...
6

7
-- 启用索引
8
ALTER TABLE users ENABLE KEYS;

1
const flattened = products.map(p => ({
2
  product_id: p._id,
3
  name: p.name,
4
  category_id: p.category.id,
5
  category_name: p.category.name,
6
  original_price: p.price.original,
7
  current_price: p.price.current,
8
  colors: p.specs.color.join(','),
9
  storage_options: p.specs.storage.join(',')
10
}));

1
CREATE TABLE products (
2
  product_id VARCHAR(24) PRIMARY KEY,
3
  name VARCHAR(200),
4
  category_id INT,
5
  category_name VARCHAR(100),
6
  original_price DECIMAL(10,2),
7
  current_price DECIMAL(10,2),
8
  colors VARCHAR(200),
9
  storage_options VARCHAR(200)
10
);

1
INSERT INTO orders (order_id, user_id, total, status) VALUES
2
(1, 100, 99.99, 'pending'),
3
(2, 101, 199.99, 'paid'),
4
... (1000 rows);

1
const batchSize = 1000;
2
for (let i = 0; i < orders.length; i += batchSize) {
3
  const batch = orders.slice(i, i + batchSize);
4
  await insertBatch(batch);
5
}

1
START TRANSACTION;
2
-- INSERT 语句
3
COMMIT;

1
[
2
  {
3
    "timestamp": "2026-01-01 00:00:00",
4
    "level": "INFO",
5
    "service": "api-gateway",
6
    "message": "User login",
7
    "context": {
8
      "userId": 123,
9
      "ip": "192.168.1.1"
10
    }
11
  }
12
]

1
CREATE TABLE logs_archive (
2
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
3
  timestamp DATETIME,
4
  level VARCHAR(20),
5
  service VARCHAR(100),
6
  message TEXT,
7
  user_id INT,
8
  ip VARCHAR(50),
9
  INDEX idx_timestamp (timestamp),
10
  INDEX idx_level (level)
11
);

1
INSERT INTO logs_archive
2
(timestamp, level, service, message, user_id, ip) VALUES
3
('2026-01-01 00:00:00', 'INFO', 'api-gateway', 'User login', 123, '192.168.1.1'),
4
...;

1
// 导出数据为 JSON
2
const data = await db.query('SELECT * FROM users');
3
fs.writeFileSync('users_backup.json', JSON.stringify(data));
4

5
// 转换为 SQL
6
const sql = jsonToSQL(data, 'users');
7
fs.writeFileSync('users_backup.sql', sql);

1
// 每天凌晨 2 点备份
2
cron.schedule('0 2 * * *', async () => {
3
  const tables = ['users', 'orders', 'products'];
4

5
  for (const table of tables) {
6
    const data = await db.query(`SELECT * FROM ${table}`);
7
    const sql = generateInsertSQL(data, table);
8
    fs.writeFileSync(`${table}_backup.sql`, sql);
9
  }
10

11
  console.log('备份完成');
12
});