将 PHP 数组保存到 MySQL？

Save PHP array to MySQL?

提问by JasonDavis

What is a good way to save an array of data to a single mysql field?

将数据数组保存到单个 mysql 字段的好方法是什么？

Also once I query for that array in the mysql table, what is a good way to get it back into array form?

另外，一旦我在 mysql 表中查询该数组，将它恢复为数组形式的好方法是什么？

Is serialize and unserialize the answer?

序列化和反序列化是答案吗？

采纳答案by Peter Lindqvist

There is no goodway to store an array into a single field.

没有好的方法可以将数组存储到单个字段中。

You need to examine your relational data and make the appropriate changes to your schema. See example below for a reference to this approach.

您需要检查您的关系数据并对您的架构进行适当的更改。有关此方法的参考，请参见下面的示例。

If you mustsave the array into a single field then the serialize()and unserialize()functions will do the trick. But you cannot perform queries on the actual content.

如果您必须将数组保存到单个字段中，那么serialize()和unserialize()函数就可以解决问题。但是您不能对实际内容执行查询。

As an alternative to the serialization function there is also json_encode()and json_decode().

作为序列化功能的替代方案，还有 json_encode()and json_decode()。

Consider the following array

考虑以下数组

$a = array(
    1 => array(
        'a' => 1,
        'b' => 2,
        'c' => 3
    ),
    2 => array(
        'a' => 1,
        'b' => 2,
        'c' => 3
    ),
);

To save it in the database you need to create a table like this

要将其保存在数据库中，您需要创建一个这样的表

$c = mysql_connect($server, $username, $password);
mysql_select_db('test');
$r = mysql_query(
    'DROP TABLE IF EXISTS test');
$r = mysql_query(
    'CREATE TABLE test (
      id INTEGER UNSIGNED NOT NULL,
      a INTEGER UNSIGNED NOT NULL,
      b INTEGER UNSIGNED NOT NULL,
      c INTEGER UNSIGNED NOT NULL,
      PRIMARY KEY (id)
    )');

To work with the records you can perform queries such as these (and yes this is an example, beware!)

要处理记录，您可以执行诸如此类的查询（是的，这是一个示例，请注意！）

function getTest() {
    $ret = array();
    $c = connect();
    $query = 'SELECT * FROM test';
    $r = mysql_query($query,$c);
    while ($o = mysql_fetch_array($r,MYSQL_ASSOC)) {
        $ret[array_shift($o)] = $o;
    }
    mysql_close($c);
    return $ret;
}
function putTest($t) {
    $c = connect();
    foreach ($t as $k => $v) {
        $query = "INSERT INTO test (id,".
                implode(',',array_keys($v)).
                ") VALUES ($k,".
                implode(',',$v).
            ")";
        $r = mysql_query($query,$c);
    }
    mysql_close($c);
}

putTest($a);
$b = getTest();

The connect()function returns a mysql connection resource

该connect()函数返回一个mysql连接资源

function connect() {
    $c = mysql_connect($server, $username, $password);
    mysql_select_db('test');
    return $c;
}

回答by Matchu

Generally, yes, serialize and unserialize are the way to go.

一般来说，是的，序列化和反序列化是要走的路。

If your data is something simple, though, saving as a comma-delimited string would probably be better for storage space. If you know that your array will just be a list of numbers, for example, then you should use implode/explode. It's the difference between 1,2,3and a:3:{i:0;i:1;i:1;i:2;i:2;i:3;}.

但是，如果您的数据很简单，保存为逗号分隔的字符串可能更适合存储空间。例如，如果你知道你的数组只是一个数字列表，那么你应该使用内爆/爆炸。这之间的区别1,2,3和a:3:{i:0;i:1;i:1;i:2;i:2;i:3;}。

If not, then serialize and unserialize work for all cases.

如果没有，则对所有情况进行序列化和反序列化工作。

回答by KramerC

Just use the serialize PHP function:

只需使用序列化 PHP 函数：

<?php
$myArray = array('1', '2');
$seralizedArray = serialize($myArray);
?>

However, if you are using simple arrays like that you might as well use implode and explode.Use a blank array instead of new.

但是，如果您使用像这样的简单数组，您不妨使用内爆和爆炸。使用空白数组而不是新数组。

回答by seaBass

Serialize/Unserialize array for storage in a DB

序列化/反序列化数组以存储在数据库中

Visit http://php.net/manual/en/function.serialize.php

访问http://php.net/manual/en/function.serialize.php

From the PHP Manual:

来自 PHP 手册：

Look under "Return" on the page

在页面上的“返回”下查看

Returns a string containing a byte-stream representation of value that can be stored anywhere.

返回一个字符串，其中包含可以存储在任何地方的值的字节流表示。

Note that this is a binary string which may include null bytes, and needs to be stored and handled as such. For example, serialize() output should generally be stored in a BLOB field in a database, rather than a CHAR or TEXT field.

请注意，这是一个可能包含空字节的二进制字符串，需要按原样进行存储和处理。例如，serialize() 输出通常应存储在数据库中的 BLOB 字段中，而不是 CHAR 或 TEXT 字段中。

Note: If you want to store html into a blob, be sure to base64 encode it or it could break the serialize function.

注意：如果要将 html 存储到 blob 中，请确保对其进行 base64 编码，否则可能会破坏序列化功能。

Example encoding:

示例编码：

$YourSerializedData = base64_encode(serialize($theHTML));

$YourSerializedDatais now ready to be stored in blob.

$YourSerializedData现在已准备好存储在 blob 中。

After getting data from blob you need to base64_decode then unserialize Example decoding:

从 blob 获取数据后，您需要 base64_decode 然后反序列化示例解码：

$theHTML = unserialize(base64_decode($YourSerializedData));

回答by Harry

The best way, that I found to myself is save array as data string with separator characters

我自己发现的最好方法是将数组保存为带有分隔符的数据字符串

$array = array("value1", "value2", "value3", "...", "valuen");
$array_data = implode("array_separator", $array);

$query = "INSERT INTO my_tbl_name (id, array_data) VALUES(NULL,'" . $array_data . "');";

You can then search data, stored in your array with simple query

然后，您可以使用简单的查询搜索存储在数组中的数据

$query = "SELECT * FROM my_tbl_name WHERE array_data LIKE '%value3%'";

use explode() function to convert "array_data" string to array

使用explode()函数将“array_data”字符串转换为数组

$array = explode("array_separator", $array_data);

note that this is not working with multidimensional arrays and make sure that your "array_separator" is unique and had not exist in array values.

请注意，这不适用于多维数组，并确保您的“array_separator”是唯一的并且不存在于数组值中。

Be careful !!! if you just will take a form data and put in database, you will be in trap, becous the form data isn't SQL-safe ! you must handle your form value with mysql_real_escape_stringor if you use MySQLi mysqli::real_escape_stringor if value are integer or boolean cast (int) (boolean) on them

当心 ！！！如果您只是将表单数据放入数据库中，您将陷入困境，因为表单数据不是 SQL 安全的！你必须用mysql_real_escape_string处理你的表单值，或者如果你使用 MySQLi mysqli::real_escape_string或者如果值是整数或布尔型转换 (int) (boolean)

$number = (int)$_POST['number'];
$checked = (boolean) $_POST['checked'];

$name = mysql_real_escape_string($db_pt, $_POST['name']);
$email = mysqli_obj->real_escape_string($_POST['email']);

回答by ceejayoz

Serialize and unserialize are pretty common for that. You could also use JSON via json_encode and json_decode for a less PHP-specific format.

序列化和反序列化非常常见。您还可以通过 json_encode 和 json_decode 使用 JSON 以获得较少的 PHP 特定格式。

回答by Sebastian Lasse

As mentioned before - If you do not need to search for data within the array, you can use serialize - but this is "php only". So I would recommend to use json_decode/ json_encode- not only for performance but also for readability and portability (other languages such as javascript can handle json_encoded data).

如前所述 - 如果您不需要在数组中搜索数据，您可以使用序列化 - 但这是“仅限 php”。所以我建议使用json_decode/ json_encode- 不仅是为了性能，也是为了可读性和可移植性（其他语言，如 javascript 可以处理 json_encoded 数据）。

回答by shylent

Uhh, I don't know why everyone suggests serializing the array.

呃，我不知道为什么每个人都建议序列化数组。

I say, the best way is to actually fit it into your database schema. I have no idea (and you gave no clues) about the actual semantic meaning of the data in your array, but there are generally two ways of storing sequences like that

我说，最好的方法是将它实际放入您的数据库模式中。我不知道（并且您没有提供任何线索）关于数组中数据的实际语义含义，但通常有两种存储序列的方法

create table mydata (
  id int not null auto_increment primary key,
  field1 int not null,
  field2 int not null,
  ...
  fieldN int not null
)

This way you are storing your array in a single row.

这样您就可以将数组存储在一行中。

create table mydata (
    id int not null auto_increment primary key,
    ...
)

create table myotherdata (
    id int not null auto_increment primary key,
    mydata_id int not null,
    sequence int not null,
    data int not null
)

The disadvantage of the first method is, obviously, that if you have many items in your array, working with that table will not be the most elegant thing. It is also impractical (possible, but quite inelegant as well - just make the columns nullable) to work with sequences of variable length.

显然，第一种方法的缺点是，如果数组中有很多项，那么使用该表将不是最优雅的事情。使用可变长度的序列也是不切实际的（可能，但也很不优雅——只需将列设为可空）。

For the second method, you can have sequences of any length, but of only one type. You can, of course, make that one type varchar or something and serialize the items of your array. Not the best thing to do, but certainly better, than serializing the whole array, right?

对于第二种方法，您可以拥有任意长度的序列，但只有一种类型。当然，您可以创建一种类型的 varchar 或其他类型并序列化数组的项。这不是最好的做法，但肯定比序列化整个数组更好，对吗？

Either way, any of this methods gets a clear advantage of being able to access an arbitrary element of the sequence and you don't have to worry about serializing arrays and ugly things like that.

无论哪种方式，这些方法中的任何一个都具有能够访问序列的任意元素的明显优势，并且您不必担心序列化数组和诸如此类的丑陋事物。

As for getting it back. Well, get the appropriate row/sequence of rows with a query and, well, use a loop.. right?

至于拿回来。好吧，通过查询获取适当的行/行序列，然后使用循环.. 对吗？

回答by Roberto Murguia

You can save your array as a json.
there is documentation for json data type: https://dev.mysql.com/doc/refman/5.7/en/json.html
I think this is the best solution, and will help you maintain your code more readable by avoiding crazy functions.
I expect this is helpful for you.

您可以将数组保存为 json。
有关于 json 数据类型的文档：https: //dev.mysql.com/doc/refman/5.7/en/json.html
我认为这是最好的解决方案，它将帮助您通过避免疯狂的功能来保持代码的可读性.
我希望这对你有帮助。

回答by Vildan Bina

you can insert serialized object ( array ) to mysql , example serialize($object)and you can unserize object example unserialize($object)

您可以将序列化对象（数组）插入到 mysql 中，例如serialize($object)，您可以取消序列化对象示例unserialize($object)