สับเปลี่ยนลำดับแถว (Shuffle) ใน MySQL ด้วย ORDER BY RAND()

ใช้ ORDER BY RAND() เพื่อสุ่มลำดับแถวทุกครั้งที่รันคิวรี่

mysql> CREATE TABLE DemoTable1557 (
    -> SubjectId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> SubjectName VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.91 sec)

mysql> INSERT INTO DemoTable1557(SubjectName) VALUES('MySQL');
Query OK, 1 row affected (0.13 sec)
mysql> INSERT INTO DemoTable1557(SubjectName) VALUES('MongoDB');
Query OK, 1 row affected (0.13 sec)
mysql> INSERT INTO DemoTable1557(SubjectName) VALUES('Java');
Query OK, 1 row affected (0.13 sec)
mysql> INSERT INTO DemoTable1557(SubjectName) VALUES('C');
Query OK, 1 row affected (0.13 sec)

mysql> SELECT * FROM DemoTable1557;

+-----------+-------------+
| SubjectId | SubjectName |
+-----------+-------------+
|         1 | MySQL       |
|         2 | MongoDB     |
|         3 | Java        |
|         4 | C           |
+-----------+-------------+
4 rows in set (0.00 sec)

สุ่มลำดับ:

mysql> SELECT * FROM DemoTable1557 ORDER BY RAND();

+-----------+-------------+
| SubjectId | SubjectName |
+-----------+-------------+
|         1 | MySQL       |
|         3 | Java        |
|         2 | MongoDB     |
|         4 | C           |
+-----------+-------------+
4 rows in set (0.00 sec)

ใช้ได้กับ: แสดงโฆษณาสุ่ม, แบบทดสอบสุ่มคำถาม, แสดงสินค้าแนะนำสุ่ม