如何将数组存储到mysql中?

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数组。然后，我将连接comments表和此表，并检查投票者数组或note中是否存在当前的用户id。如果这样做，投票图标将被禁用。我想我不会用这种方式在循环中使用mysql查询。

Do you happen to know any better ways?

你知道更好的方法吗?

11 个解决方案

#1

You can always serialize the array and store that in the database.
PHP Serialize

您总是可以序列化数组并将其存储在数据库中。PHP序列化

You can then unserialize the array when needed.

然后，您可以在需要时对数组进行非序列化。

#2

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 table comments_votes will prevent users from voting multiple times on the same comments.

交集表comments_votes上的复合主键(comment_id, user_id)将阻止用户对同一注释进行多次投票。

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 InnoDB storage engine, it will also be wise to use foreign key constraints on the comment_id and user_id fields of the intersection table. However note that MyISAM, the default storage engine in MySQL, does not enforce foreign key constraints:

如果您将使用InnoDB存储引擎，那么明智的做法是在交集表的comment_id和user_id字段上使用外键约束。但是请注意，MySQL中的默认存储引擎ismyam并不强制执行外键约束:

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_votes will never have a comment_id or user_id value that doesn't exist in the comments and users tables, 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).

这些外键保证comments_votes中的一行永远不会有各自在comments和users表中不存在的comment_id或user_id值。外键不需要有一个可工作的关系数据库，但是它们对于避免关系破裂和孤立行(例如)是必不可少的。参照完整性)。

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.

事实上，如果要将序列化的数组存储到单个数据库字段中，则很难实现引用完整性。

#3

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.

此外，你可能有未来的需要，比如“显示用户已投出的所有选票”，取消特定的投票或限制每天最多的投票数。使用规范化结构来实现这些操作非常简单和快速，而且在序列化数组中执行起来非常缓慢和复杂。

#4

you should have three tables: users, comments and comment_users.

您应该有三个表:user、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 :)

这样你的性能就可以达到最大值:)

#5

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只接受每个用户一次投票和评论。

#6

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 Comments and Users with respective id's

通过这种方式，您可以检查用户是否发布了评论。除此之外，还应该有注释和具有各自id的用户的表

#7

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

然后，当您从数据库中提取数据时，使用blow()函数

$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!

像这样!

#8

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 unserialize函数解码数据。

I think you should visit this page on storing array in mysql.

我认为你应该访问这个页面关于在mysql中存储数组。

#9

Storing with json or serialized array 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.

注意:如果手动更改序列化数据，则需要注意字符计数。

#10

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

#11

Use this:

用这个:

$string=implode("-",array_of_userIds);

store it in database. Then while extracting it from database, use

将其存储在数据库中。然后，在从数据库中提取它时，使用它

$my_arr=explode("-",$retrivedstring);