我有一个名为HWC的表,我想通过随机行,我的数据库有大约10k个条目,我之前使用的方法太慢了。
这是我的查询:
SELECT *
FROM HWC AS r1 JOIN
(SELECT (RAND() *
(SELECT MAX(id)
FROM HWC)) AS id)
AS r2 WHERE r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 1
下面是我在PHP中得到的错误:
Error Number: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM HWC AS r1 JOIN (SELECT (RAND() * (SELECT MAX(id) FROM HWC)) AS id) A' at line 2
SELECT * FROM (`SELECT` * FROM HWC AS r1 JOIN (SELECT (RAND() * (SELECT MAX(id) FROM HWC)) AS id) AS r2 WHERE r1.id >= r2.id ORDER BY r1.id ASC LIMIT 1)
My Full PHP Code for the model:
function random()
{
$query = "SELECT *
FROM HWC AS r1 JOIN
(SELECT (RAND() *
(SELECT MAX(id)
FROM HWC)) AS id)
AS r2 WHERE r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 1";
$result = $this->db->get($query);
return $result->result_array();
}
我的get()应该是query();
去掉第二个SELECT
:
SELECT * FROM (SELECT * FROM HWC ...