时间戳

首页 > 解决方案 > 如何在 MySQL 中存储和获取封装的数组数据

mysql - 如何在 MySQL 中存储和获取封装的数组数据

问题描述

我想为我的 WebApplication 编写一个 CMS。现在我使用 PHP 作为数据源

"bladderTanks" => array(
            "en" => array(
                "0" => "0",
                "1" => "bladder accumulator for up to 1 spare stroke (1x90°)",
                "2" => "bladder accumulator for up to 2 spare stroke (2x90°)",
                "3" => "bladder accumulator for up to 3 spare stroke (3x90°)"
            ),
            "de" => array(
                "0" => "0",
                "1" => "Blasenspeicher mit bis zu 1 Stellfahrt (1x90°)",
                "2" => "Blasenspeicher mit bis zu 2 Stellfahrt (2x90°)",
                "3" => "Blasenspeicher mit bis zu 3 Stellfahrt (3x90°)"
            )
        )

var amountBladder = 1;
var language = "de";

var neededValue = bladderTanks[language][amountBladder]

或者

"signalVoltage" => array(
            "en" => array(
                "12" => "12 VDC potential-free, separate cable, power supplied by customer",
                "24" => "24 VDC potential-free, separate cable, power supplied by customer",
                "56" => "56 VDC potential-free, separate cable, power supplied by customer",
                "110" => "110 VDC potential-free, separate cable, power supplied by customer",
                "115" => "115 VAC potential-free, separate cable, power supplied by customer",
                "230" => "230 VAC potential-free, separate cable, power supplied by customer"
            ),
            "de" => array(
                "12" => "12 VDC potentialfrei, seperates Kabel, Versorgung durch Kunde",
                "24" => "24 VDC potentialfrei, seperates Kabel, Versorgung durch Kunde",
                "56" => "56 VDC potentialfrei, seperates Kabel, Versorgung durch Kunde",
                "110" => "110 VDC potentialfrei, seperates Kabel, Versorgung durch Kunde",
                "115" => "115 VAC potentialfrei, seperates Kabel, Versorgung durch Kunde",
                "230" => "230 VAC potentialfrei, seperates Kabel, Versorgung durch Kunde"
            )
        ),

每次在代码（PHP 后端）中修改它很烦人。所以我想为我实现德语和英语两种语言的用户界面。

我不知道在 MySQL 中管理这些封装的最佳方法是什么。

这是一个希望可以理解的示例代码笔

标签： mysqlarraysjson

解决方案

如果您想使用 MySQL 进行本地化，您需要一个名为语言的表，例如：

语言表：

id (PRIMARY KEY) | language (VARCHAR) |
         1              en
         2              de

膀胱坦克表：

  id (PRIMARY KEY) | language_id (reference) | amount_bladder (UNSIGNED INT) | text (VARCHAR)         |
         1                  1                       0                              0
         2                  1                       1                         bladder accumulator for up to 1 spare stroke (1x90°)                 
         3                  2                       0                              0

依此类推，你明白了。

因此，现在当您需要英文版本时，您只获取 language_id 1 的记录：

  SELECT * FROM bladder_tanks WHERE language_id = 1 ORDER BY amount_bladder

保存或更新也是如此：

  UPDATE bladder_tanks SET text = "blah blah" WHERE amount_bladder = 1 AND language_id = 1
  INSERT INTO bladder_tanks (language_id, amount_bladder, text) VALUES (1, 1, "blah blah")

您的控制器操作/功能可能看起来像这样的伪：

  function display() {
      // SELECT amount_bladder, text FROM bladder_tanks WHERE language_id = 1 ORDER BY amount_bladder;
  }

  function store(array $bladderTanks) {
       foreach ($bladderTanks as $bladderTank) {
           // insert query
       }
  }

  function update(array $bladderTanks) {
       foreach ($bladderTanks as $bladderTank) {
           // update query
       }
  }

希望这能让您对它应该如何工作有一个基本的了解。我试图让它尽可能简单，所以我放弃了 created_at、updated_at 额外的列（但是你应该根据自己的需要来构建它）

推荐阅读