如何将数据（php）传递到数据库（mysql）以获得最佳性能？

Question

I created a php system that generates 150 "Serial Number" for each product. Now I want to store these serial numbers in a database. There are some ways that I found to do this:

1. Create a table name products_serialNumber and store each serialnumber to 1 record and relate it to products using product_id key.

If I do this, I will have lots of data that may no body use that. But fetching data in this way is more easier and faster.

1. Create a table name product_serialNumbers and store array of serialnumbers for each products and relate is using "product_id" key again.

If I do like this way, I will have a serial numbers record for each products, but I should use more foreach and for loop functions to extract array of data and variables to use where that I need, and use lots of memory. I'm limited to 64m using memory.

Answer 1

You Can Store JSON …

Normalization is a technique used to optimize the database structure. The First Normal Form (1NF) rule governs that every column should hold a single value — which is broken by storing multi-value JSON documents.

Create a Table with a JSON Field

CREATE TABLE `product_serialNumbers` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `serialNumbers` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Adding JSON Data

INSERT INTO `product_serialNumbers` (`title`, `serialNumbers`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["ac111", "ab177", "ac14521"]'
);

JSON can also be created with the:

//returns [1, 2, "abc"]:
SELECT JSON_ARRAY(1, 2, 'abc');

//returns {"a": 1, "b": 2}:
SELECT JSON_OBJECT('a', 1, 'b', 2);

//returns ["a", 1, {"key": "value"}]:
SELECT JSON_MERGE('["a", 1]', '{"key": "value"}');

Searching JSON Data

//all product_serialNumbers with the 'ac14521' serialNumbers:
SELECT * FROM `product_serialNumbers` 
WHERE JSON_CONTAINS(serialNumbers, '["ac14521"]');