php 如何将数组存储到mysql中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3413291/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to store an array into mysql?
提问by King Julien
Is there a way to store an array into mysql field? I'm creating a comment rating system so I want to store the arrays of user ids to prevent multiple votings. I'm going to create new table that holds the comment id and the array of user ids who have voted on this comment. Than I'll join comments table and this table and check whether the current user id exists in the voters array or note. If it does than the voting icons would be disabled. I think I'll prevent to use mysql query in loop in this way.
有没有办法将数组存储到 mysql 字段中？我正在创建一个评论评级系统，所以我想存储用户 ID 数组以防止多次投票。我将创建一个新表，其中包含评论 ID 和对该评论进行投票的用户 ID 数组。然后我将加入评论表和这个表并检查当前用户 ID 是否存在于选民数组或注释中。如果这样做，则投票图标将被禁用。我想我会阻止以这种方式在循环中使用 mysql 查询。
Do you happen to know any better ways?
你碰巧知道任何更好的方法吗？
回答by Daniel Vassallo
You may want to tackle this as follows:
您可能希望按如下方式解决此问题：
CREATE TABLE comments (
comment_id int,
body varchar(100),
PRIMARY KEY (comment_id)
);
CREATE TABLE users (
user_id int,
username varchar(20),
PRIMARY KEY (user_id)
);
CREATE TABLE comments_votes (
comment_id int,
user_id int,
vote_type int,
PRIMARY KEY (comment_id, user_id)
);
The composite primary key(comment_id, user_id)on the intersection tablecomments_voteswill prevent users from voting multiple times on the same comments.
交集表上的复合主键将防止用户对同一评论进行多次投票。(comment_id, user_id)comments_votes
Let's insert some data in the above schema:
让我们在上面的模式中插入一些数据：
INSERT INTO comments VALUES (1, 'first comment');
INSERT INTO comments VALUES (2, 'second comment');
INSERT INTO comments VALUES (3, 'third comment');
INSERT INTO users VALUES (1, 'user_a');
INSERT INTO users VALUES (2, 'user_b');
INSERT INTO users VALUES (3, 'user_c');
Now let's add some votes for user 1:
现在让我们为用户 1 添加一些投票：
INSERT INTO comments_votes VALUES (1, 1, 1);
INSERT INTO comments_votes VALUES (2, 1, 1);
The above means that user 1 gave a vote of type 1 on comments 1 and 2.
以上表示用户 1 对评论 1 和 2 进行了类型 1 的投票。
If the same user tries to vote again on one of those comments, the database will reject it:
如果同一用户尝试再次对这些评论之一进行投票，数据库将拒绝它：
INSERT INTO comments_votes VALUES (1, 1, 1);
ERROR 1062 (23000): Duplicate entry '1-1' for key 'PRIMARY'
If you will be using the InnoDBstorage engine, it will also be wise to use foreign keyconstraints on the comment_idand user_idfields of the intersection table. However note that MyISAM, the default storage engine in MySQL, does not enforce foreign key constraints:
如果您将使用InnoDB存储引擎，那么在交集表的和字段上使用外键约束也是明智的。但是请注意，MySQL 中的默认存储引擎MyISAM不强制执行外键约束：comment_iduser_id
CREATE TABLE comments (
comment_id int,
body varchar(100),
PRIMARY KEY (comment_id)
) ENGINE=INNODB;
CREATE TABLE users (
user_id int,
username varchar(20),
PRIMARY KEY (user_id)
) ENGINE=INNODB;
CREATE TABLE comments_votes (
comment_id int,
user_id int,
vote_type int,
PRIMARY KEY (comment_id, user_id),
FOREIGN KEY (comment_id) REFERENCES comments (comment_id),
FOREIGN KEY (user_id) REFERENCES users (user_id)
) ENGINE=INNODB;
These foreign keys guarantee that a row in comments_voteswill never have a comment_idor user_idvalue that doesn't exist in the commentsand userstables, respectively. Foreign keys aren't required to have a working relational database, but they are definitely essential to avoid broken relationships and orphan rows (ie. referential integrity).
这些外键分别保证 in 中的行comments_votes永远不会有和表中不存在的comment_idoruser_id值。外键不需要具有工作关系数据库，但它们对于避免断开的关系和孤立行（即参照完整性）绝对是必不可少的。commentsusers
In fact, referential integrity is something that would have been very difficult to enforce if you were to store serialized arrays into a single database field.
事实上，如果您要将序列化数组存储到单个数据库字段中，那么参照完整性是很难实施的。
回答by Grant Collins
You can always serialize the array and store that in the database.
PHP Serialize
您始终可以序列化数组并将其存储在数据库中。
PHP 序列化
You can then unserialize the array when needed.
然后，您可以在需要时对数组进行反序列化。
回答by Pekka
Consider normalizing the table structure into a comments, and a separate votes table.
考虑将表结构规范化为评论和单独的投票表。
Table "comments":
表“评论”：
id
comment
user
...
Table "votes":
表“投票”：
user_id
comment_id
vote (downvote/upvote)
this would allow an unlimited number of votes without having to deal with the limits of a database field.
这将允许无限数量的投票，而不必处理数据库字段的限制。
Also, you may have future needs for operations like "show all votes a user has cast", removing specific votes or limiting the maximum number of votes per day. These operations are dead easy and fast to implement with a normalized structure, and horribly slow and complex in a serialized array.
此外，您可能有未来的操作需求，例如“显示用户已投的所有选票”、删除特定选票或限制每天的最大选票数。这些操作使用规范化结构实现起来非常容易和快速，而在序列化数组中则非常缓慢和复杂。
回答by Dennis Haarbrink
you should have three tables: users, comments and comment_users.
您应该拥有三个表：users、comments 和 comment_users。
comment_users has just two fields: fk_user_id and fk_comment_id
comment_users 只有两个字段：fk_user_id 和 fk_comment_id
That way you can keep your performance up to a maximum :)
这样你就可以最大限度地保持你的表现:)
回答by Bj?rn
I'd prefer to normalize your table structure more, something like;
我更喜欢规范化你的表结构，比如；
COMMENTS
-------
id (pk)
title
comment
userId
USERS
-----
id (pk)
name
email
COMMENT_VOTE
------------
commentId (pk)
userId (pk)
rating (float)
Now it's easier to maintain! And MySQL only accept one vote per user and comment.
现在更容易维护！而 MySQL 只接受每个用户和评论一票。
回答by Srinivas Reddy Thatiparthy
create table like this,
像这样创建表，
CommentId UserId
---------------------
1 usr1
1 usr2
In this way you can check whether the user posted the comments are not..
Apart from this there should be tables for Commentsand Userswith respective id's
通过这种方式，你可以检查用户是否发布的评论都没有..除了这个应该有表的Comments和Users与各自的ID
回答by Hardline_98
If you just store the data in a database as you would if you were manually putting it into an array
如果您只是将数据存储在数据库中，就像手动将其放入数组一样
"INSERT INTO database_name.database_table (`array`)
VALUES
('One,Two,Three,Four')";
Then when you are pulling from the database, use the explode() function
然后当你从数据库中拉取时，使用explode()函数
$sql = mysql_query("SELECT * FROM database_name.database_table");
$numrows = mysql_num_rows($sql);
if($numrows != 0){
while($rows = mysql_fetch_assoc($sql)){
$array_from_db = $rows['array'];
}
}else{
echo "No rows found!".mysql_error();
}
$array = explode(",",$array_from_db);
foreach($array as $varchar){
echo $varchar."<br/>";
}
Like so!
像这样！
回答by B?ny?min Ak??y
Storing with jsonor serializedarray is the best solution for now. With some situations (trimming " ' characters) json might be getting trouble but serialize should be great choice.
使用json或序列化数组存储是目前最好的解决方案。在某些情况下（修剪 " ' 字符）json 可能会遇到问题，但序列化应该是不错的选择。
Note: If you change serialized data manually, you need to be careful about character count.
注意：如果您手动更改序列化数据，则需要注意字符数。
回答by Shubham Kumar
You can use the php serialize function to store array in MySQL.
您可以使用 php serialize 函数在 MySQL 中存储数组。
<?php
$array = array("Name"=>"Shubham","Age"=>"17","website"=>"http://mycodingtricks.com");
$string_array = serialize($array);
echo $string_array;
?>
It's output will be :
它的输出将是：
a:3{s:4:"Name";s:7:"Shubham";s:3:"Age";s:2:"17";s:7:"website";s:25:"http://mycodingtricks.com";}
And then you can use the php unserialize function to decode the data.
然后你可以使用 php 反序列化函数来解码数据。
I think you should visit this page on storing array in mysql.
我认为您应该访问有关在 mysql 中存储数组的页面。
回答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
您可以将数组保存为 json。
有 json 数据类型的文档：https: //dev.mysql.com/doc/refman/5.7/en/json.html
