Hiển thị hàng ngẫu nhiên từ bảng MySQL

Dùng ORDER BY RAND() LIMIT 1. Tạo bảng:

mysql> CREATE TABLE DemoTable (
    Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(50),
    Quote TEXT
); Query OK, 0 rows affected (0.71 sec)

Chèn dữ liệu:

mysql> INSERT INTO DemoTable(Name,Quote) VALUES('Chris','MySQL is a relational database'); Query OK, 1 row affected (0.11 sec)
mysql> INSERT INTO DemoTable(Name,Quote) VALUES('Robert','Java is an Object Oriented Programming Language'); Query OK, 1 row affected (0.17 sec)
mysql> INSERT INTO DemoTable(Name,Quote) VALUES('Mike','C is a Procedural Language'); Query OK, 1 row affected (0.15 sec)
mysql> INSERT INTO DemoTable(Name,Quote) VALUES('David','Hibernate and spring is a framework'); Query OK, 1 row affected (0.15 sec)

Hiển thị dữ liệu:

mysql> SELECT * FROM DemoTable;

Kết quả:

+----+--------+-------------------------------------------------+
| Id | Name   | Quote                                           |
+----+--------+-------------------------------------------------+
| 1  | Chris  | MySQL is a relational database                  |
| 2  | Robert | Java is an Object Oriented Programming Language |
| 3  | Mike   | C is a Procedural Language                      |
| 4  | David  | Hibernate and spring is a framework             |
+----+--------+-------------------------------------------------+
4 rows in set (0.00 sec)

Truy vấn lấy 1 hàng ngẫu nhiên:

mysql> SELECT Id, Name, Quote FROM DemoTable ORDER BY RAND() LIMIT 1;

Kết quả (ví dụ):

+----+------+----------------------------+
| Id | Name | Quote                      |
+----+------+----------------------------+
| 3  | Mike | C is a Procedural Language |
+----+------+----------------------------+
1 row in set (0.00 sec)

Lưu ý hiệu năng: ORDER BY RAND() chậm trên bảng lớn (phải sort toàn bộ). Cách tối ưu: lấy MAX(Id), random trong ứng dụng, rồi WHERE Id >= random_id LIMIT 1.