MySQL随机查询一条记录

表结构

CREATE TABLE `poet` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `dynasty` varchar(11) DEFAULT NULL,
  `author` text DEFAULT NULL,
  `paragraph` text DEFAULT NULL,
  `strains` text DEFAULT NULL,
  `title` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `poet_title` (`title`(10)) USING BTREE,
  KEY `poet_dynasty` (`dynasty`) USING HASH,
  KEY `poet_author` (`author`(10)) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=311862 DEFAULT CHARSET=utf8mb4;

查询语句

SELECT r1.title,
       r1.author,
       r1.paragraph
FROM poet AS r1
JOIN
  (SELECT CEIL(RAND() *
                 (SELECT MAX(id)
                  FROM poet)) AS id) AS r2
WHERE r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 1;

结果样例：

*************************** 1. row ***************************
    title: 雜詠下 林禽
   author: 洪适
paragraph: 蒙潤碧千顆，迎曦紅半頰。閱古憩鵝池，牽連青李帖。
1 row in set (0.001 sec)

若要查询随机的一条author为李白的记录，此处使用存储过程来实现：

DELIMITER $

CREATE DEFINER=`app`@`%` PROCEDURE `random_poet_of_libai`( )
BEGIN
SET @_rand_index := CEIL( RAND( ) * ( SELECT COUNT( * ) FROM poet WHERE author = '李白' ) );
PREPARE stmt FROM 'SELECT title, author, paragraph FROM poet WHERE author = \'李白\' LIMIT ?, 1;';
EXECUTE stmt USING @_rand_index;
DEALLOCATE PREPARE stmt;
END

DELIMITER ;

CALL random_poet_of_libai();

结果样例：

*************************** 1. row ***************************
    title: 雜曲歌辭 鳴雁行
   author: 李白
paragraph: 胡雁鳴，辭燕山，昨發委羽朝度關。一一銜蘆枝，南飛散落天地間。連行接翼往復還，客居煙波寄湘吳。凌霜觸雪毛體枯，畏逢矰繳驚相呼。
1 row in set (0.008 sec)

参考

(196 words)