mysql常用命令

1.从某张表随机取一条记录

简单，低效

SELECT * FROM table_name ORDER BY RAND() LIMIT 1；

速度快，效率高

SELECT t1.id, t1.word, t1.STATUS FROM hy_idiom AS t1
    JOIN (
        SELECT ROUND(
                RAND() * (( SELECT MAX( id ) FROM hy_idiom WHERE STATUS = 1 )-( SELECT MIN( id ) FROM hy_idiom WHERE STATUS = 1 ))+
                ( SELECT MIN( id ) FROM hy_idiom WHERE STATUS = 1 )
            ) AS id 
    ) AS t2 
WHERE t1.id >= t2.id AND t1.STATUS = 1 ORDER BY t1.id LIMIT 5;

SELECT T1.* FROM (

    SELECT (@RN := @RN + 1) AS sort, U.* FROM t_user U, (SELECT @RN := -1) AS T11
    
) T1 JOIN(

    SELECT FLOOR( rand()*( SELECT COUNT(1) FROM t_user ) ) AS sort
    
) T2 ON T1.sort = T2.sort

2.从表总记录数取随机序号

SELECT FLOOR( rand()*( SELECT COUNT(*) FROM t_user ) );

3.显示某张表序号/行号

SELECT (@RN := @RN + 1) AS sort, id FROM t_user, (SELECT @RN := -1) AS T0