用于随机化帖子的SQL [重复]

In my SQL i have a table with a column id & Order.On website data is displayed according to id & Order.But we want to change the way data is displayed we want to randomize whole content..Can anyone please suggest SQL query for this.

rough database structure is below

city_id       city_name       city_order
  1               xyz             1
  2               geh             2
  3               tre             3

Regards,

</div>

You would do something like this:

select city_id, city_name, city_order from table_name order by rand()

where table_name is the name of your table

Add ORDER BY RAND() to the end of your query

You can use ORDER BY RAND() like others have suggested, but then you should also take a moment to wave goodbye to any semblance of efficiency and performance that your query may once have had. This is because you're now ordering your result set based on something that is:

1. Not indexed.
2. Dynamically generated.

MySQL [and pretty much any other RDBMS] is most decidedly unsuited for nearly any operation that requires introducing randomness.

Better options:

1. If your anticipated result set is sufficiently small to not cause:

   • Delays based on the bandwidth required between your web and database server.
   • Excessive memory use storing the result set.

   Then simply get all of the potential content items and use array_rand() or shuffle() to get your random selection.

2. If your anticipated result set is going to be large enough that you don't want to pass the whole thing to PHP then I suggest something like:

   $sql = 
   "SELECT COUNT(*) 'count'
   FROM content_table
   WHERE [your conditions]";
   
   $rs = $dbh->query($query);
   
   $random = rand(0,$rs['count']);
   
   $sql =
   "SELECT field1, field2, ...
   FROM content_table
   WHERE [your conditions]
   LIMIT $random, 1";
   
   $rs = $dbh->query($sql);
   

Both of which will respect your indexes.