多行或一列[sql性能]
我正在为这个简单的想法而苦苦挣扎。 看看reddit，当你注册一个新帐户时..你会自动订阅一些默认频道。
我想对我的网站做同样的事情。
我知道我可以用 id, user_id, chan_id 做简单而愚蠢的 user_chan
如果 chan 是这样的：
ID | NAME
1 | videos
2 | pictures
user_chan 会像这样（我我是用户 1)
ID | USER_ID | CHAN_ID
1 | 1 | 1
2 | 1 | 2
我想在这里说得非常清楚 :D
我想这就是 reddit 的工作原理。但每次用户注册时，他们都必须插入十几行。我猜他们有大量的用户！
那么像这样的 user 表内的解决方案是否更聪明？：
ID | USER_NICKNAME | CHANS
1 | me | 1,2
I'm struggling with this simple idea.
Look at reddit, when you sign up for a new account.. you automatically subscribe to some default channels.
I want to do the same with my website.
I know I could do the simple and stupid user_chan with id, user_id, chan_id
if chan is like this :
ID | NAME
1 | videos
2 | pictures
user_chan would be like this (i'm user 1)
ID | USER_ID | CHAN_ID
1 | 1 | 1
2 | 1 | 2
I'm trying to be very clear here :D
I guess that's how reddit works. But every time a user signs up, they must have to insert a dozen of rows. And I guess they have tons of users !!
So is a solution inside user table like this more clever ?:
ID | USER_NICKNAME | CHANS
1 | me | 1,2
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
不要使用一列来存储多个值。这是一种非规范化，稍后会带来痛苦。例如，当您需要查询哪个用户拥有频道 3 时，您将不得不使用
LIKE，这会表现得很差。当您需要删除用户拥有的多个频道中的一个时，事情会变得更加困难。对于大多数数据库，您可以使用一个
INSERT语句插入多行。确切的语法因平台而异；在 SQL Server 中，您可以这样做：Do not use one column to store multiple values. This is a denormalization that will cause pain later. E.g., when you need to query which user has channel 3, you are going to have to use
LIKE, which will perform badly. When you need to remove just one channel of the several the user has, it gets even harder.With most databases, you can insert multiple rows with one
INSERTstatement. The exact syntax varies by platform; in SQL Server, you can do this:或者，按照您的建议使用 XML 列来存储多个值。它可以有一个 XML 索引，并且可以像关系表一样进行查询/更新等（尽管语法更复杂）。
Or, use an XML column to store the multiple values as you suggested. This can have an XML index and can be queried / updated etc as you would a relational table (albeit more complex syntax).