Làm việc với kiểu dữ liệu JSON trong MySQL

Cơ sở dữ liệu SQL có xu hướng cứng nhắc.

Nếu bạn đã làm việc với họ, bạn sẽ đồng ý rằng thiết kế cơ sở dữ liệu mặc dù nó có vẻ dễ dàng hơn, là thực tế phức tạp hơn nhiều. Cơ sở dữ liệu SQL tin vào cấu trúc, đó là lý do tại sao nó được gọi là ngôn ngữ truy vấn có cấu trúc.

Ở phía bên kia đường chân trời, chúng ta có cơ sở dữ liệu NoSQL, còn được gọi là các cơ sở dữ liệu ít lược đồ nhằm khuyến khích sự linh hoạt. Trong cơ sở dữ liệu ít giản đồ hơn, không có giới hạn cấu trúc áp đặt, chỉ cần lưu dữ liệu.

Mặc dù mọi công cụ đều có trường hợp sử dụng, nhưng đôi khi mọi thứ lại đòi hỏi cách tiếp cận lai.

Điều gì sẽ xảy ra nếu bạn có thể cấu trúc một số bộ phận cơ sở dữ liệu và để những người khác linh động?

MySQL phiên bản 5.7.8 giới thiệu một kiểu dữ liệu JSON cho phép bạn hoàn thành điều đó.

Trong hướng dẫn này, bạn sẽ học.

• Làm thế nào để thiết kế bảng cơ sở dữ liệu của bạn bằng cách sử dụng các lĩnh vực JSON.
• Các chức năng dựa trên JSON khác nhau có sẵn trong MYSQL để tạo, đọc, cập nhật và xóa hàng.
• Làm thế nào để làm việc với các lĩnh vực JSON sử dụng ORM Tham vọng trong Laravel.

Tại thời điểm này, bạn có thể tự hỏi mình tại sao bạn lại muốn sử dụng JSON khi MySQL đã đáp ứng được nhiều nhu cầu về cơ sở dữ liệu ngay cả trước khi nó giới thiệu một kiểu dữ liệu JSON.

Câu trả lời nằm trong các trường hợp sử dụng mà bạn có thể sử dụng cách tiếp cận thực hiện thay đổi.

Hãy để tôi giải thích với một ví dụ.

Giả sử bạn đang xây dựng một ứng dụng web, nơi bạn phải lưu cấu hình / sở thích của người dùng trong cơ sở dữ liệu.

Nói chung, bạn có thể tạo một bảng cơ sở dữ liệu riêng biệt với id, user_id, key, và valuecác lĩnh vực hoặc lưu nó như là một chuỗi định dạng mà bạn có thể phân tích tại thời gian chạy.

Tuy nhiên, điều này hoạt động tốt cho một số lượng nhỏ người dùng. Nếu bạn có khoảng một nghìn người dùng và năm phím cấu hình, bạn đang tìm kiếm một bảng với 5000 bản ghi lưu trữ một tính năng rất nhỏ trong ứng dụng của bạn.

Hoặc nếu bạn đang dùng định dạng chuỗi tuyến đường, mã không liên quan chỉ làm cho tải máy chủ của bạn.

Sử dụng trường dữ liệu JSON để lưu cấu hình của người dùng trong trường hợp như vậy có thể giải phóng không gian của bảng cơ sở dữ liệu và giảm số lượng các bản ghi, được lưu riêng rẽ, giống như số lượng người dùng.

Và bạn sẽ nhận được lợi ích bổ sung là không phải viết bất kỳ mã phân tích JSON nào, ORM hoặc thời gian chạy ngôn ngữ sẽ giải quyết nó.

Trước khi chúng ta lặn vào sử dụng tất cả các công cụ JSON mát mẻ trong MySQL, chúng ta sẽ cần một cơ sở dữ liệu mẫu để chơi với.

Vì vậy, chúng ta hãy lấy lược đồ cơ sở dữ liệu của chúng ta ra trước.

Chúng tôi sẽ xem xét trường hợp sử dụng của một cửa hàng trực tuyến chứa nhiều thương hiệu và nhiều loại thiết bị điện tử.

Vì các thiết bị điện tử khác nhau có các thuộc tính khác nhau (so sánh Macbook với Máy hút bụi) mà người mua quan tâm, điển hình là mẫu mô hình Thuộc tính-Thuộc tính (EAV) được sử dụng.

Tuy nhiên, vì bây giờ chúng ta có tùy chọn để sử dụng kiểu dữ liệu JSON, chúng ta sẽ bỏ EAV.

Đối với một sự khởi đầu, cơ sở dữ liệu của chúng tôi sẽ được đặt tên e_storevà có ba bảng chỉ tên, brands, categories, và productstương ứng.

Các bảng brandsvà categoriesbảng của chúng ta sẽ khá giống nhau, mỗi bảng có một idvà một nametrường.

CREATE DATABASE IF NOT EXISTS `e_store`
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;

SET default_storage_engine = INNODB;

CREATE TABLE `e_store`.`brands`(
    `id` INT UNSIGNED NOT NULL auto_increment ,
    `name` VARCHAR(250) NOT NULL ,
    PRIMARY KEY(`id`)
);

CREATE TABLE `e_store`.`categories`(
    `id` INT UNSIGNED NOT NULL auto_increment ,
    `name` VARCHAR(250) NOT NULL ,
    PRIMARY KEY(`id`)
);

Mục tiêu của hai bảng này là để chứa các loại sản phẩm và thương hiệu cung cấp các sản phẩm này.

Trong khi chúng tôi đang ở đó, chúng ta hãy tiếp tục và đưa một số dữ liệu vào các bảng này để sử dụng sau.

/* Brands */
INSERT INTO `e_store`.`brands`(`name`)
VALUES
    ('Samsung');

INSERT INTO `e_store`.`brands`(`name`)
VALUES
    ('Nokia');

INSERT INTO `e_store`.`brands`(`name`)
VALUES
    ('Canon');

/* Types of electronic device */
INSERT INTO `e_store`.`categories`(`name`)
VALUES
    ('Television');

INSERT INTO `e_store`.`categories`(`name`)
VALUES
    ('Mobilephone');

INSERT INTO `e_store`.`categories`(`name`)
VALUES
    ('Camera');

Bảng thương hiệu

Bảng phân loại

Tiếp theo, là lĩnh vực kinh doanh của hướng dẫn này.

Chúng tôi sẽ tạo ra một productsbảng với id, name, brand_id, category_id, và attributescác lĩnh vực.

CREATE TABLE `e_store`.`products`(
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
    `name` VARCHAR(250) NOT NULL ,
    `brand_id` INT UNSIGNED NOT NULL ,
    `category_id` INT UNSIGNED NOT NULL ,
    `attributes` JSON NOT NULL ,
    PRIMARY KEY(`id`) ,
    INDEX `CATEGORY_ID`(`category_id` ASC) ,
    INDEX `BRAND_ID`(`brand_id` ASC) ,
    CONSTRAINT `brand_id` FOREIGN KEY(`brand_id`) REFERENCES `e_store`.`brands`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE ,
    CONSTRAINT `category_id` FOREIGN KEY(`category_id`) REFERENCES `e_store`.`categories`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE
);

Định nghĩa bảng của chúng tôi xác định các ràng buộc khoá ngoại đối với trường brand_idvà category_idtrường, xác định rằng chúng tham chiếu đến bảng brandsvà categoriestương ứng. Chúng tôi cũng đã chỉ định rằng các hàng được tham chiếu không được phép xóa và nếu được cập nhật, các thay đổi cũng nên phản ánh trong tài liệu tham khảo là tốt.

Kiểu attributescột của trường đã được khai báo là JSON là kiểu dữ liệu gốc có sẵn trong MySQL. Điều này cho phép chúng ta sử dụng các cấu trúc JSON liên quan khác nhau trong MySQL trên attributeslĩnh vực của chúng tôi .

Đây là một sơ đồ quan hệ thực thể của cơ sở dữ liệu được tạo ra của chúng tôi.

Cơ sở dữ liệu e_store

Thiết kế cơ sở dữ liệu của chúng tôi không phải là tốt nhất về hiệu quả và chính xác. Không có cột giá trong productsbảng và chúng tôi có thể làm gì với việc đưa sản phẩm vào nhiều loại. Tuy nhiên, mục đích của hướng dẫn này không phải là để dạy thiết kế cơ sở dữ liệu mà là làm thế nào để mô hình các đối tượng có tính chất khác nhau trong một bảng bằng cách sử dụng các tính năng JSON của MySQL.

Chúng ta hãy xem cách tạo, đọc, cập nhật và xóa dữ liệu trong một trường JSON.

Create

Tạo một bản ghi trong cơ sở dữ liệu với một trường JSON khá đơn giản.

Tất cả bạn cần làm là thêm JSON hợp lệ làm giá trị trường trong câu lệnh chèn của bạn.

/* Let's sell some televisions */
INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Prime' ,
    '1' ,
    '1' ,
    '{"screen": "50 inch", "resolution": "2048 x 1152 pixels", "ports": {"hdmi": 1, "usb": 3}, "speakers": {"left": "10 watt", "right": "10 watt"}}'
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Octoview' ,
    '1' ,
    '1' ,
    '{"screen": "40 inch", "resolution": "1920 x 1080 pixels", "ports": {"hdmi": 1, "usb": 2}, "speakers": {"left": "10 watt", "right": "10 watt"}}'
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Dreamer' ,
    '1' ,
    '1' ,
    '{"screen": "30 inch", "resolution": "1600 x 900 pixles", "ports": {"hdmi": 1, "usb": 1}, "speakers": {"left": "10 watt", "right": "10 watt"}}'
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Bravia' ,
    '1' ,
    '1' ,
    '{"screen": "25 inch", "resolution": "1366 x 768 pixels", "ports": {"hdmi": 1, "usb": 0}, "speakers": {"left": "5 watt", "right": "5 watt"}}'
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Proton' ,
    '1' ,
    '1' ,
    '{"screen": "20 inch", "resolution": "1280 x 720 pixels", "ports": {"hdmi": 0, "usb": 0}, "speakers": {"left": "5 watt", "right": "5 watt"}}'
);

Bảng sản phẩm sau khi thêm tivi

Thay vì tự đặt ra đối tượng JSON, bạn cũng có thể sử dụng JSON_OBJECTchức năng tích hợp.

Các JSON_OBJECTchức năng chấp nhận một danh sách các cặp khóa / giá trị theo hình thức JSON_OBJECT(key1, value1, key2, value2, ... key(n), value(n))và trả về một đối tượng JSON.

/* Let's sell some mobilephones */
INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Desire' ,
    '2' ,
    '2' ,
    JSON_OBJECT(
        "network" ,
        JSON_ARRAY("GSM" , "CDMA" , "HSPA" , "EVDO") ,
        "body" ,
        "5.11 x 2.59 x 0.46 inches" ,
        "weight" ,
        "143 grams" ,
        "sim" ,
        "Micro-SIM" ,
        "display" ,
        "4.5 inches" ,
        "resolution" ,
        "720 x 1280 pixels" ,
        "os" ,
        "Android Jellybean v4.3"
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Passion' ,
    '2' ,
    '2' ,
    JSON_OBJECT(
        "network" ,
        JSON_ARRAY("GSM" , "CDMA" , "HSPA") ,
        "body" ,
        "6.11 x 3.59 x 0.46 inches" ,
        "weight" ,
        "145 grams" ,
        "sim" ,
        "Micro-SIM" ,
        "display" ,
        "4.5 inches" ,
        "resolution" ,
        "720 x 1280 pixels" ,
        "os" ,
        "Android Jellybean v4.3"
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Emotion' ,
    '2' ,
    '2' ,
    JSON_OBJECT(
        "network" ,
        JSON_ARRAY("GSM" , "CDMA" , "EVDO") ,
        "body" ,
        "5.50 x 2.50 x 0.50 inches" ,
        "weight" ,
        "125 grams" ,
        "sim" ,
        "Micro-SIM" ,
        "display" ,
        "5.00 inches" ,
        "resolution" ,
        "720 x 1280 pixels" ,
        "os" ,
        "Android KitKat v4.3"
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Sensation' ,
    '2' ,
    '2' ,
    JSON_OBJECT(
        "network" ,
        JSON_ARRAY("GSM" , "HSPA" , "EVDO") ,
        "body" ,
        "4.00 x 2.00 x 0.75 inches" ,
        "weight" ,
        "150 grams" ,
        "sim" ,
        "Micro-SIM" ,
        "display" ,
        "3.5 inches" ,
        "resolution" ,
        "720 x 1280 pixels" ,
        "os" ,
        "Android Lollypop v4.3"
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Joy' ,
    '2' ,
    '2' ,
    JSON_OBJECT(
        "network" ,
        JSON_ARRAY("CDMA" , "HSPA" , "EVDO") ,
        "body" ,
        "7.00 x 3.50 x 0.25 inches" ,
        "weight" ,
        "250 grams" ,
        "sim" ,
        "Micro-SIM" ,
        "display" ,
        "6.5 inches" ,
        "resolution" ,
        "1920 x 1080 pixels" ,
        "os" ,
        "Android Marshmallow v4.3"
    )
);

Bảng sản phẩm sau khi thêm điện thoại di động

Chú ý JSON_ARRAYhàm trả về mảng JSON khi truyền một tập các giá trị.

Nếu bạn chỉ định một phím nhiều lần, chỉ cặp khóa / giá trị đầu tiên sẽ được giữ lại. Điều này được gọi là chuẩn hóa JSON trong các điều khoản của MySQL. Ngoài ra, như là một phần bình thường, các phím đối tượng được sắp xếp và thêm khoảng trắng giữa các cặp khóa / giá trị được loại bỏ.

Một chức năng khác mà chúng ta có thể sử dụng để tạo các đối tượng JSON là JSON_MERGEhàm.

Các JSON_MERGEchức năng có nhiều đối tượng JSON và tạo ra một, tổng hợp đối tượng duy nhất.

/* Let's sell some cameras */
INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Explorer' ,
    '3' ,
    '3' ,
    JSON_MERGE(
        '{"sensor_type": "CMOS"}' ,
        '{"processor": "Digic DV III"}' ,
        '{"scanning_system": "progressive"}' ,
        '{"mount_type": "PL"}' ,
        '{"monitor_type": "LCD"}'
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Runner' ,
    '3' ,
    '3' ,
    JSON_MERGE(
        JSON_OBJECT("sensor_type" , "CMOS") ,
        JSON_OBJECT("processor" , "Digic DV II") ,
        JSON_OBJECT("scanning_system" , "progressive") ,
        JSON_OBJECT("mount_type" , "PL") ,
        JSON_OBJECT("monitor_type" , "LED")
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Traveler' ,
    '3' ,
    '3' ,
    JSON_MERGE(
        JSON_OBJECT("sensor_type" , "CMOS") ,
        '{"processor": "Digic DV II"}' ,
        '{"scanning_system": "progressive"}' ,
        '{"mount_type": "PL"}' ,
        '{"monitor_type": "LCD"}'
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Walker' ,
    '3' ,
    '3' ,
    JSON_MERGE(
        '{"sensor_type": "CMOS"}' ,
        '{"processor": "Digic DV I"}' ,
        '{"scanning_system": "progressive"}' ,
        '{"mount_type": "PL"}' ,
        '{"monitor_type": "LED"}'
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Jumper' ,
    '3' ,
    '3' ,
    JSON_MERGE(
        '{"sensor_type": "CMOS"}' ,
        '{"processor": "Digic DV I"}' ,
        '{"scanning_system": "progressive"}' ,
        '{"mount_type": "PL"}' ,
        '{"monitor_type": "LCD"}'
    )
);

Bảng sản phẩm sau khi thêm máy ảnh

Có rất nhiều điều xảy ra trong các câu lệnh chèn này và nó có thể gây nhầm lẫn. Tuy nhiên, nó là khá đơn giản.

Chúng ta chỉ truyền các đối tượng đến JSON_MERGEchức năng. Một số trong số chúng đã được xây dựng bằng cách sử dụng JSON_OBJECTchức năng mà chúng ta đã thấy trước đó trong khi các phần khác đã được truyền như là chuỗi JSON hợp lệ.

Trong trường hợp JSON_MERGEchức năng, nếu một phím được lặp đi lặp lại nhiều lần, giá trị của nó sẽ được giữ lại như một mảng trong đầu ra.

Một bằng chứng của khái niệm là theo thứ tự tôi giả sử.

/* output: {"network": ["GSM", "CDMA", "HSPA", "EVDO"]} */
SELECT JSON_MERGE(
    '{"network": "GSM"}' ,
    '{"network": "CDMA"}' ,
    '{"network": "HSPA"}' ,
    '{"network": "EVDO"}'
);

Chúng ta có thể xác nhận tất cả các truy vấn của chúng tôi đã được chạy thành công bằng cách sử dụng các JSON_TYPEchức năng mà cho chúng ta các loại giá trị trường.

/* output: OBJECT */
SELECT JSON_TYPE(attributes) FROM `e_store`.`products`;
Thêm thuộc tính là đối tượng JSON

Read

Phải, chúng tôi có một vài sản phẩm trong cơ sở dữ liệu của chúng tôi để làm việc.

Đối với các giá trị MySQL điển hình không thuộc kiểu JSON, mệnh đề where ở vị trí khá thẳng. Chỉ cần chỉ định cột, toán tử, và các giá trị bạn cần làm việc.

Tóm lại, khi làm việc với các cột JSON, điều này không hiệu quả.

/* It's not that simple */
SELECT
    *
FROM
    `e_store`.`products`
WHERE
    attributes = '{"ports": {"usb": 3, "hdmi": 1}, "screen": "50 inch", "speakers": {"left": "10 watt", "right": "10 watt"}, "resolution": "2048 x 1152 pixels"}';

Khi bạn muốn thu hẹp các hàng bằng trường JSON, bạn nên làm quen với khái niệm biểu thức đường dẫn.

Định nghĩa đơn giản nhất về biểu thức đường dẫn (nghĩ rằng bộ chọn JQuery) được sử dụng để chỉ định phần nào của tài liệu JSON hoạt động.

Phần thứ hai của câu đố là JSON_EXTRACTchức năng chấp nhận một biểu thức đường dẫn để điều hướng qua JSON.

Hãy để chúng tôi nói rằng chúng tôi quan tâm đến phạm vi của truyền hình có ít nhất một cổng USB và HDMI.

SELECT
    *
FROM
    `e_store`.`products`
WHERE
    `category_id` = 1
AND JSON_EXTRACT(`attributes` , '$.ports.usb') > 0
AND JSON_EXTRACT(`attributes` , '$.ports.hdmi') > 0;

Chọn các bản ghi theo thuộc tính JSON Đối số đầu tiên của JSON_EXTRACThàm là JSON để áp dụng biểu thức đường dẫn đến attributescột. Các biểu tượng tokenizes đối tượng để làm việc với. Các .ports.usbvà $$ports.hdmibiểu thức đường dẫn dịch để "lấy chìa khóa usb dưới cổng" và "lấy cái chìa khóa hdmi dưới cổng" tương ứng.

Một khi chúng tôi đã chiết xuất các phím chúng tôi quan tâm, nó là khá đơn giản để sử dụng các nhà khai thác MySQL như >trên chúng.

Ngoài ra, JSON_EXTRACTchức năng có bí danh ->mà bạn có thể sử dụng để làm cho truy vấn của bạn dễ đọc hơn.

Sửa lại truy vấn trước đó của chúng tôi.

SELECT
    *
FROM
    `e_store`.`products`
WHERE
    `category_id` = 1
AND `attributes` -> '$.ports.usb' > 0
AND `attributes` -> '$.ports.hdmi' > 0;

Update

Để cập nhật giá trị JSON, chúng ta sẽ sử dụng JSON_INSERT, JSON_REPLACEvà JSON_SETcác chức năng. Các hàm này cũng yêu cầu một biểu thức đường dẫn để xác định các phần của đối tượng JSON cần sửa đổi.

Đầu ra của các hàm này là một đối tượng JSON hợp lệ với những thay đổi được áp dụng.

Hãy để chúng tôi sửa đổi tất cả các điện thoại di động để có một tài sản chipset là tốt.

UPDATE `e_store`.`products`
SET `attributes` = JSON_INSERT(
    `attributes` ,
    '$.chipset' ,
    'Qualcomm'
)
WHERE
    `category_id` = 2;

Điện thoại di động cập nhật

Các $$chipsetbiểu thức đường dẫn xác định vị trí của các chipsettài sản để được ở gốc của đối tượng.

Hãy để chúng tôi cập nhật các thuộc chipsettính để được thêm mô tả bằng cách sử dụng JSON_REPLACEchức năng.

UPDATE `e_store`.`products`
SET `attributes` = JSON_REPLACE(
    `attributes` ,
    '$.chipset' ,
    'Qualcomm Snapdragon'
)
WHERE
    `category_id` = 2;

Điện thoại di động cập nhật

Dễ dàng peasy!

Cuối cùng, chúng tôi có JSON_SETchức năng mà chúng tôi sẽ sử dụng để chỉ định TV của chúng tôi là khá đầy màu sắc.

UPDATE `e_store`.`products`
SET `attributes` = JSON_SET(
    `attributes` ,
    '$.body_color' ,
    'red'
)
WHERE
    `category_id` = 1;

TV cập nhật

Tất cả những chức năng này có vẻ giống nhau nhưng có một sự khác biệt trong cách họ cư xử.

Các JSON_INSERTchức năng sẽ chỉ làm tăng thêm tài sản cho đối tượng nếu nó không tồn tại rồi.

Các JSON_REPLACEchức năng thay thế các tài sản duy nhất nếu nó được tìm thấy.

Các JSON_SETchức năng sẽ bổ sung thêm tài sản nếu nó không được tìm thấy gì khác thay thế nó.

Delete

Có hai phần để xóa mà chúng tôi sẽ xem xét.

Đầu tiên là xóa một khóa / giá trị nhất định khỏi cột JSON của bạn, trong khi thứ hai là xóa các hàng sử dụng cột JSON.

Hãy cho chúng tôi biết chúng tôi không còn cung cấp mount_typethông tin cho máy ảnh và muốn xóa nó cho tất cả các máy ảnh.

Chúng ta sẽ làm điều đó bằng cách sử dụng JSON_REMOVEhàm trả về giá trị JSON đã cập nhật sau khi gỡ bỏ khoá được xác định dựa trên biểu thức đường dẫn.

UPDATE `e_store`.`products`
SET `attributes` = JSON_REMOVE(`attributes` , '$.mount_type')
WHERE
    `category_id` = 3;

Máy ảnh sau khi gỡ bỏ thuộc tính mount_type

Đối với trường hợp thứ hai, chúng tôi cũng không cung cấp điện thoại di động nữa mà có phiên bản Jellybean của hệ điều hành Android.

DELETE FROM `e_store`.`products`
WHERE `category_id` = 2
AND JSON_EXTRACT(`attributes` , '$.os') LIKE '%Jellybean%';

Chúng tôi không bán Jellybeans nữa!

Như đã đề cập trước đó, làm việc với một thuộc tính cụ thể đòi hỏi phải sử dụng JSON_EXTRACTchức năng này để áp dụng LIKEtoán tử, trước tiên chúng ta đã khai thác ostài sản của mobilephones (với sự trợ giúp của category_id) và xóa tất cả các bản ghi có chứa chuỗi Jellybean.

Những ngày tháng làm việc trực tiếp với cơ sở dữ liệu đang ở đằng sau chúng ta.

Ngày nay, các khuôn khổ bảo vệ các nhà phát triển khỏi các hoạt động cấp thấp hơn và nó hầu như cảm thấy xa lạ đối với một khuôn khổ cuồng tín không thể dịch được kiến thức cơ sở dữ liệu của mình thành một người lập bản đồ quan hệ đối tượng.

Với mục đích không để các nhà phát triển như vậy đau lòng và tự hỏi về sự tồn tại và mục đích của họ trong vũ trụ, chúng ta sẽ xem xét làm thế nào để đi về kinh doanh của các cột JSON trong khuôn khổ Laravel.

Chúng tôi sẽ chỉ tập trung vào các phần trùng lặp với chủ đề của chúng tôi đề cập đến các cột JSON. Một hướng dẫn chuyên sâu về khung công tác Laravel vượt quá phạm vi của phần này.

Đảm bảo cấu hình ứng dụng Laravel của bạn để sử dụng cơ sở dữ liệu MySQL.

Chúng tôi sẽ tạo ra ba di cư cho brands, categoriesvà products tương ứng.

$ php artisan make:migration create_brands
$ php artisan make:migration create_categories
$ php artisan make:migration create_products

Việc di chuyển create_brandsvà create_categorieslà khá tương tự và và một quy định cho các nhà phát triển Laravel.

/* database/migrations/create_brands.php */

<?php

use IlluminateSupportFacadesSchema;
use IlluminateDatabaseSchemaBlueprint;
use IlluminateDatabaseMigrationsMigration;

class CreateBrands extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('brands', function(Blueprint $table){
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('brands');
    }
}

/* database/migrations/create_categories.php */

<?php

use IlluminateSupportFacadesSchema;
use IlluminateDatabaseSchemaBlueprint;
use IlluminateDatabaseMigrationsMigration;

class CreateCategories extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('categories', function(Blueprint $table){
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('categories');
    }
}

Việc create_productsdi chuyển cũng sẽ có các chỉ thị cho các chỉ mục và khoá nước ngoài.

/* database/migrations/create_products */

<?php

use IlluminateSupportFacadesSchema;
use IlluminateDatabaseSchemaBlueprint;
use IlluminateDatabaseMigrationsMigration;

class CreateProducts extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('products', function(Blueprint $table){
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('name');
            $table->unsignedInteger('brand_id');
            $table->unsignedInteger('category_id');
            $table->json('attributes');
            $table->timestamps();
            // foreign key constraints
            $table->foreign('brand_id')->references('id')->on('brands')->onDelete('restrict')->onUpdate('cascade');
            $table->foreign('category_id')->references('id')->on('categories')->onDelete('restrict')->onUpdate('cascade');
            // indexes
            $table->index('brand_id');
            $table->index('category_id');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('products');
    }
}

Chú ý đến $$able->json('attributes');tuyên bố trong quá trình di chuyển.

Cũng giống như việc tạo bất kỳ trường bảng nào bằng cách sử dụng kiểu dữ liệu có tên thích hợp, chúng tôi đã tạo một cột JSON sử dụng jsonphương pháp có tên attributes.

Ngoài ra, điều này chỉ hoạt động cho các công cụ cơ sở dữ liệu hỗ trợ các kiểu dữ liệu JSON.

Các động cơ, chẳng hạn như các phiên bản cũ của MySQL sẽ không thể thực hiện các di chuyển này.

Khác với hiệp hội, không cần thiết phải thiết lập mô hình của chúng tôi nên chúng ta hãy chạy nhanh họ.

/* app/Brand.php */

<?php

namespace App;

use IlluminateDatabaseEloquentModel;

class Brand extends Model
{
    // A brand has many products
    public function products(){
        return $this->hasMany('Product')
    }
}

/* app/Category.php */

<?php

namespace App;

use IlluminateDatabaseEloquentModel;

class Category extends Model
{
    // A category has many products
    public function products(){
        return $this->hasMany('Product')
    }
}

/* app/Product.php */

<?php

namespace App;

use IlluminateDatabaseEloquentModel;

class Product extends Model
{
    // Cast attributes JSON to array
    protected $casts = [
        'attributes' => 'array'
    ];

    // Each product has a brand
    public function brand(){
        return $this->belongsTo('Brand');
    }

    // Each product has a category
    public function category(){
        return $this->belongsTo('Category');
    }
}

Một lần nữa, Productmô hình của chúng tôi cần một đề cập đặc biệt.

Các $$astsmảng trong đó có chìa khóa attributescác thiết lập để arrayđảm bảo bất cứ khi nào một sản phẩm được lấy từ cơ sở dữ liệu, đó là attributesJSON được chuyển đổi sang một mảng liên quan.

Chúng ta sẽ thấy sau trong hướng dẫn làm thế nào điều này tạo điều kiện cho chúng ta cập nhật hồ sơ từ các hành động điều khiển của chúng tôi.

Tạo một sản phẩm

Phát biểu của bảng điều khiển dành cho quản trị viên, các thông số để tạo ra một sản phẩm có thể đi qua các tuyến đường khác nhau vì chúng tôi có một số loại sản phẩm. Bạn cũng có thể có các chế độ xem khác nhau để tạo, chỉnh sửa, hiển thị và xóa một sản phẩm.

Ví dụ: một biểu mẫu để thêm máy ảnh yêu cầu các trường nhập khác nhau hơn là một biểu mẫu để thêm điện thoại di động để chúng bảo đảm các chế độ xem riêng biệt.

Hơn nữa, khi bạn có dữ liệu đầu vào của người dùng, bạn sẽ có thể chạy nó thông qua trình xác nhận yêu cầu, riêng cho máy ảnh và mỗi điện thoại di động.

Bước cuối cùng là tạo ra sản phẩm thông qua Sự say mê.

Chúng tôi sẽ tập trung vào tài nguyên máy ảnh cho phần còn lại của hướng dẫn này. Các sản phẩm khác có thể được giải quyết bằng cách sử dụng mã sản xuất theo cách tương tự.

Giả sử chúng ta đang lưu một máy ảnh và các lĩnh vực biểu mẫu được đặt tên là các thuộc tính máy ảnh tương ứng, đây là hành động điều khiển.

// creates product in database
// using form fields
public function store(Request $request){
    // create object and set properties
    $camera = new AppProduct();
    $camera->name = $request->name;
    $camera->brand_id = $request->brand_id;
    $camera->category_id = $request->category_id;
    $camera->attributes = json_encode([
        'processor' => $request->processor,
        'sensor_type' => $request->sensor_type,
        'monitor_type' => $request->monitor_type,
        'scanning_system' => $request->scanning_system,
    ]);
    // save to database
    $camera->save();
    // show the created camera
    return view('product.camera.show', ['camera' => $camera]);
}

Tìm nạp sản phẩm

Nhớ lại $$astsmảng mà chúng ta tuyên bố trước đó trong Productmô hình. Nó sẽ giúp chúng ta đọc và sửa một sản phẩm bằng cách xử lý các thuộc tính như một mảng kết hợp.

// fetches a single product
// from database
public function show($id){
    $camera = AppProduct::find($id);
    return view('product.camera.show', ['camera' => $camera]);
}

Quan điểm của bạn sẽ sử dụng $$amerabiến theo cách sau.

<table>
    <tr>
        <td>Name</td>
        <td>{{ $camera->name }}</td>
    </tr>
    <tr>
        <td>Brand ID</td>
        <td>{{ $camera->brand_id }}</td>
    </tr>
    <tr>
        <td>Category ID</td>
        <td>{{ $camera->category_id }}</td>
    </tr>
    <tr>
        <td>Processor</td>
        <td>{{ $camera->attributes['processor'] }}</td>
    </tr>
    <tr>
        <td>Sensor Type</td>
        <td>{{ $camera->attributes['sensor_type'] }}</td>
    </tr>
    <tr>
        <td>Monitor Type</td>
        <td>{{ $camera->attributes['monitor_type'] }}</td>
    </tr>
    <tr>
        <td>Scanning System</td>
        <td>{{ $camera->attributes['scanning_system'] }}</td>
    </tr>
</table>

Chỉnh sửa sản phẩm

Như được hiển thị trong phần trước, bạn có thể dễ dàng tìm nạp sản phẩm và chuyển nó đến chế độ xem, trong trường hợp này sẽ là chế độ xem chỉnh sửa.

Bạn có thể sử dụng biến sản phẩm để điền trước các trường biểu mẫu trên trang chỉnh sửa.

Việc cập nhật sản phẩm dựa vào đầu vào của người dùng sẽ khá giống với storehành động mà chúng ta đã thấy trước đó, thay vì tạo một sản phẩm mới, bạn sẽ lấy nó từ cơ sở dữ liệu trước khi cập nhật.

Tìm kiếm dựa trên các thuộc tính JSON

Phần cuối cùng của câu đố vẫn còn để thảo luận là truy vấn các cột JSON sử dụng ORM say mê.

Nếu bạn có trang tìm kiếm cho phép tìm kiếm các camera dựa trên các thông số kỹ thuật do người dùng cung cấp, bạn có thể làm như sau với mã sau đây.

// searches cameras by user provided specifications
public function search(Request $request){
    $cameras = AppProduct::where([
        ['attributes->processor', 'like', $request->processor],
        ['attributes->sensor_type', 'like', $request->sensor_type],
        ['attributes->monitor_type', 'like', $request->monitor_type],
        ['attributes->scanning_system', 'like', $request->scanning_system]
    ])->get();
    return view('product.camera.search', ['cameras' => $cameras]);
}

Các bản ghi thu được bây giờ sẽ có sẵn cho product.camera.searchchế độ xem dưới dạng một $$amerasbộ sưu tập.

Xóa một sản phẩm

Sử dụng thuộc tính cột không phải là JSON, bạn có thể xóa các sản phẩm bằng cách chỉ định mệnh đề where và sau đó gọi deletephương thức đó.

Ví dụ, trong trường hợp của một ID.

AppProduct::where('id', $id)->delete();
Đối với các cột JSON, chỉ định mệnh đề where sử dụng một thuộc tính đơn hoặc nhiều và sau đó gọi deletephương thức.

// deletes all cameras with the sensor_type attribute as CMOS
AppProduct::where('attributes->sensor_type', 'CMOS')->delete();
}

Chúng ta hầu như không bị trầy xước bề mặt khi sử dụng cột JSON trong MySQL.

Bất cứ khi nào bạn cần lưu dữ liệu dưới dạng cặp khóa / giá trị trong một bảng riêng biệt hoặc làm việc với các thuộc tính linh hoạt cho một thực thể, bạn nên xem xét sử dụng trường loại dữ liệu JSON vì nó có thể đóng góp rất nhiều cho việc nén thiết kế cơ sở dữ liệu của bạn.

Nếu bạn quan tâm đến việc lặn sâu hơn, tài liệu MySQL là một tài nguyên tuyệt vời để khám phá khái niệm JSON hơn nữa.

Tôi hy vọng bạn tìm thấy hướng dẫn này thú vị và am hiểu. Cho đến đoạn tiếp theo của tôi, vui mã hóa!

• Working with JSON in MySQL