你如何使用MySQL更新附加到行？

问题描述：

I am making a basic CMS system which allows for page edits by registered users.

I wish to store a list of users who have submitted some page content to each a specific page. In my database table for pages, I have a column called contributed, and in there I want to have all the user id’s of each user who contributed to each page, separated with a common. Looking something like this ...

Page Name | Author | Comments | Contributed
---------------------------------------------------------------
Home      | 1      | 0        |1, 2, 3, 4
About     | 1      | 0        |1, 3, 4, 7, 9
Contact   | 2      | 0        |2, 4
Download  | 8      | 0        |8

Using MySql, how can I write an update query that appends another user id to this column when a user makes a contribution instead of updating the entire row, removing the ids of those who have previously contributed?

我正在建立一个基本的CMS系统，允许注册用户进行页面编辑。 p>

我希望存储已向每个特定页面提交了一些页面内容的用户列表。在我的页面数据库表中，我有一个名为contributions的列，在那里我希望拥有每个用户的所有用户ID，每个用户为每个页面做出贡献，用一个公用分隔。看起来像这样... p>

 页面名称| 作者| 评论| 供稿
 -----------------------------------------------  ---------------- 
首页|  1 |  0 | 1,2,3,4 
关于|  1 |  0 | 1,3,4,7,9 
接触|  2 |  0 | 2,4，n下载|  8 |  0 | 8 
  code>  pre> 
 
 使用MySql，当用户做出贡献而不是更新整行时，如何编写一个更新查询，将另一个用户ID附加到此列 ，删除以前贡献的人的ID？   p> 
  div>

答

For this, it would be best to create some sort of history table. Example:

**page_history**

id
page_id //ID of the page
user_id //ID of the user making the change
date //Date and time of the change
from //The old content
to //The changed content

Then you'd insert a new row into page_history every time a user edits a page. Note: You should read up on the concept of Database Normalization.

答

I might end up doing this on one level up - without any knowledge of size of your CMS:

CREATE TABLE test(pageid INT, contributed VARCHAR(1024));
INSERT INTO test(pageid, contributed) VALUES (1, "kala");
UPDATE test SET contributed = CONCAT((SELECT contributed FROM (SELECT * FROM test) as a WHERE pageid = 1),',','new_user_name') WHERE pageid = 1;

For SELECT * FROM test there is good description at You can't specify target table for update in FROM clause

你如何使用MySQL更新附加到行？

相关推荐