doushihu5475 2017-02-19 01:57
浏览 25
已采纳

如何将数据(php)传递到数据库(mysql)以获得最佳性能?

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.

  • 写回答

3条回答 默认 最新

  • dtxooq1020 2017-02-19 03:23
    关注

    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"]');
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)
编辑
预览

报告相同问题?

悬赏问题

  • ¥50 数据库开发问题求解答
  • ¥15 安装anaconda时报错
  • ¥20 如何用Python处理单元格内连续出现的重复词语?
  • ¥15 小程序有个导出到插件方式,我是在分包下引入的插件,这个export的路径对吗,我看官方文档上写的是相对路径
  • ¥20 希望有人能帮我完成这个设计( *ˊᵕˋ)
  • ¥100 将Intptr传入SetHdevmode()将Intptr传入后转换为DEVMODE的值与外部代码不一致
  • ¥50 基于ERA5数据计算VPD
  • ¥15 寻找杂志《Tornatzky, L. G., & Fleischer, M. (1990). The Processes of Technological Innovation. 》
  • ¥15 前台多人编辑时怎么让每个人保存刷新都互不干扰
  • ¥20 如何用Python删除单元格内连续出现的重复词?