แปลง JSON เป็นคำสั่ง MySQL!

ป้อน JSON เพื่อแปลงเป็นคำสั่ง CREATE TABLE และ INSERT! รองรับ MySQL, MariaDB
JSON
ผลลัพธ์

แปลง JSON เป็นคำสั่ง MySQL!

ดูค่าต่าง ๆ ในอาร์เรย์ JSON แล้วอนุมานไทป์คอลัมน์ของ MySQL/MariaDB และสร้างคำสั่ง CREATE TABLE พร้อม INSERT หนึ่งคำสั่งที่มีทุกแถว วางผลลัพธ์ในไคลเอนต์ MySQL แล้วตารางจะถูกสร้างและเติมข้อมูลในครั้งเดียว

มีประโยชน์เมื่ออยากโหลดข้อมูลจาก API ภายนอกลงตารางชั่วคราวเพื่อสรุปผลด้วย SQL หรือใส่แถวทดสอบลงฐานข้อมูลสำหรับพัฒนา

วิธีใช้

  1. วางอาร์เรย์ของออบเจ็กต์ลงในช่อง JSON ถ้าเป็นออบเจ็กต์เดียวจะได้ INSERT หนึ่งแถว
  2. ใส่ชื่อตารางที่จะสร้างใน ชื่อตาราง (ค่าเริ่มต้น my_table) ชื่อนี้ถูกเก็บใน URL เป็น ?table=
  3. ตรวจไทป์คอลัมน์ที่อนุมานได้ ปรับความยาวหรือ index ตามต้องการ แล้วรัน SQL ใน MySQL Workbench, DBeaver หรือ CLI mysql

ตัวอย่าง

อินพุต (JSON)
[
  { "id": 1, "email": "minji@example.com", "plan": "pro", "verified": true,
    "settings": { "lang": "ko" }, "joinedAt": "2024-03-01T09:30:00Z" },
  { "id": 2, "email": "tom@example.com", "plan": "free", "verified": false,
    "settings": null, "joinedAt": "2024-03-04T23:10:00Z" }
]
เอาต์พุต (MySQL)
CREATE TABLE `members` (
  `id` INT NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `plan` VARCHAR(255) NOT NULL,
  `verified` TINYINT(1) NOT NULL,
  `settings` JSON NULL,
  `joinedAt` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `members` (`id`, `email`, `plan`, `verified`, `settings`, `joinedAt`) VALUES
  (1, 'minji@example.com', 'pro', 1, '{"lang":"ko"}', '2024-03-01 09:30:00'),
  (2, 'tom@example.com', 'free', 0, NULL, '2024-03-04 23:10:00');

verified กลายเป็น TINYINT(1) และ settings ที่เก็บออบเจ็กต์กลายเป็นคอลัมน์ JSON ที่ยอมให้เป็น NULL เพราะมีแถวหนึ่งเป็น null วันที่ ISO ที่ลงท้ายด้วย Z (UTC) ถูกเขียนใหม่เป็นรูปแบบ '2024-03-01 09:30:00' ที่ MySQL รับได้ และเพราะ id เป็นจำนวนเต็มที่มีในทุกแถว จึงกลายเป็น primary key

ฟีเจอร์ที่รองรับ

  • การอนุมานไทป์: บูลีน → TINYINT(1), จำนวนเต็ม 32 บิต → INT, จำนวนเต็มที่ใหญ่กว่า → BIGINT, ทศนิยม → DOUBLE, ออบเจ็กต์และอาร์เรย์ → JSON
  • รูปแบบสตริง: 2024-01-02 09:00:00 หรือ ISO 8601 → DATETIME, 2024-01-02 → DATE, 09:30 → TIME, นอกนั้น → VARCHAR(255) หรือ TEXT ถ้ามีค่าใดยาวเกิน 255 ตัวอักษร
  • เมื่อแต่ละแถวไม่ตรงกัน ไทป์ที่กว้างกว่าจะชนะ: จำนวนเต็ม + ทศนิยม → DOUBLE, วันที่ + วันเวลา → DATETIME, ผสมแบบอื่น → VARCHAR(255)
  • คอลัมน์จะเป็น NULL ถ้ามีแถวใดเป็น null หรือไม่มีคีย์นั้น นอกนั้นเป็น NOT NULL
  • คีย์ id ถูกใช้เป็น PRIMARY KEY ถ้าไม่มี จะเพิ่มคอลัมน์ id BIGINT NOT NULL AUTO_INCREMENT
  • identifier ถูกครอบด้วย backtick และ ' กับ \ ในสตริงถูก escape ตารางถูกสร้างด้วย ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

กรณีการใช้งาน

สรุปข้อมูลจาก API ด้วย SQL

ถ้ามีรายการชำระเงินหรือ log ที่บันทึกเป็น JSON จาก API การโหลดลงตารางแล้วรัน GROUP BY มักเร็วกว่าการเขียนสคริปต์

ข้อมูลทดสอบสำหรับฐานข้อมูล dev และ staging

JSON จากเครื่องมือสร้าง mock data หรือ response จาก production ที่เก็บไว้ จะกลายเป็นคำสั่ง INSERT สำหรับทดสอบ migration หรือพัฒนา UI

ข้อควรระวังและข้อจำกัด

  • ชื่อคอลัมน์คือคีย์ JSON ตามเดิม camelCase อย่าง joinedAt จะไม่ถูกแปลงเป็น snake_case
  • ออบเจ็กต์ที่ซ้อนกันไม่ถูก normalize แยกเป็นตาราง แต่ใส่ลงคอลัมน์ JSON คอลัมน์เดียว
  • ไม่อนุมานความยาวของ VARCHAR(255) index, constraint UNIQUE และความแม่นยำของ DECIMAL สำหรับคอลัมน์เงิน แนะนำให้เปลี่ยน DOUBLE เป็น DECIMAL
  • ถ้า id มีค่า null ตารางจะถูกสร้างโดยไม่มี primary key
  • วันเวลาที่มีเขตเวลาจะถูกแปลงเป็น UTC ก่อนเก็บ ถ้าต้องการค่าเวลาท้องถิ่น ให้ปรับเอง
  • ทุกแถวอยู่ใน INSERT คำสั่งเดียว ถ้ามีหลายหมื่นแถวอาจชนขีดจำกัด max_allowed_packet ของเซิร์ฟเวอร์

คำถามที่พบบ่อย

ผลลัพธ์รันบน PostgreSQL หรือ SQLite ได้ไหม?

ใช้ตรง ๆ ไม่ได้ เพราะใช้ไวยากรณ์ของ MySQL อย่าง identifier แบบ backtick, ENGINE=InnoDB, TINYINT(1) และ AUTO_INCREMENT ให้เปลี่ยน backtick เป็นเครื่องหมายคำพูดคู่ และ AUTO_INCREMENT เป็น GENERATED ... AS IDENTITY หรือสิ่งที่เทียบเท่า

ทำไมเป็น TINYINT(1) ไม่ใช่ BOOLEAN?

ใน MySQL BOOLEAN เป็นแค่ชื่อแฝงของ TINYINT(1) ค่าถูก insert เป็น 1/0 และชื่อไทป์ตรงกับที่ dump และ ORM แสดง

คอลัมน์ JSON ใช้กับเซิร์ฟเวอร์รุ่นเก่าได้ไหม?

ไทป์ JSON มีตั้งแต่ MySQL 5.7.8 และ MariaDB 10.2 (ซึ่งเป็นชื่อแฝงของ LONGTEXT) ถ้าเก่ากว่านั้นให้เปลี่ยนเป็น TEXT