SQL中如何按指定ID获取随机帖子？

Hey there! Let's break down your issue step by step to get you the result you want:

First, the syntax error in your query #

Your current WHERE item_id= 1,2,3,4 is invalid SQL syntax. The = operator only works for matching a single value — when you want to target a list of specific IDs, you need to use the IN keyword instead. That's why your query isn't working right now.

The correct way to combine specific IDs with random ordering #

If your goal is to fetch all 4 posts with IDs 1,2,3,4 and return them in a random order, here's the fixed query:

SELECT * FROM mt_item WHERE item_id IN (1, 2, 3, 4) ORDER BY RAND()

Since you're only selecting 4 specific IDs, the LIMIT 4 is technically redundant (the result set will never have more than 4 rows), but you can keep it if you prefer for clarity.

If instead you wanted to randomly pick a subset of these posts (say, 2 out of the 4), just add a LIMIT clause with your desired number:

SELECT * FROM mt_item WHERE item_id IN (1, 2, 3, 4) ORDER BY RAND() LIMIT 2

A quick note on database-specific random functions #

Keep in mind that RAND() is MySQL-specific. If you're using a different database:

• PostgreSQL uses RANDOM()
• SQL Server uses ORDER BY NEWID() (or CHECKSUM(NEWID(), item_id) for more consistent randomness)
  Based on your table name mt_item, it looks like you're working with a Movable Type setup which typically uses MySQL, so RAND() should work perfectly for you.

内容的提问来源于stack exchange，提问作者Katzenliebe