mysql随机数怎么取整,mysql 随机取值

怎么从mysql表中随机取数据

从mysql数据库表中随机获取数据

创新互联公司专注于肃北网站建设服务及定制，我们拥有丰富的企业做网站经验。 热诚为您提供肃北营销型网站建设，肃北网站制作、肃北网页设计、肃北网站官网定制、小程序制作服务，打造肃北网络公司原创品牌,更为您提供肃北网站排名全网营销落地服务。

其实，从数据库随机获取数据，很简单，只须用到一个rand()函数;

select * from table_name order by rand() limit 0,5;

下面是一个小实例：

从文章表中随机获取5条数据。

$dblink=mysql_connect("localhost","root","123456");

mysql_query("set names utf8");

mysql_select_db("aixuexi");

$sql="select * from waxx_article order by rand() limit 0,5";

$rs=mysql_query($sql);

while($row=mysql_fetch_array($rs)){

$rows[]=$row;

}

if($rows){

foreach($rows as $v){

?

div style="width:350px;height:35px;line-height:35px;border:1px solid #ccc;"?php echo $v['title']; ?/div

?php

}

}else{

echo "暂无文章";

}

mysql去掉小数取整（不是四舍五入），该怎么处理

这个问题，直接在源头解决；

Mysql数据库表里面，设置你要取整的字段的数据类型为int，即可

不用写进去是小数，用的时候再取整，这么麻烦

MySQL中的RAND()函数使用

rand() 随机生成 0 - 1的浮点数 , 常与其他函数结合使用 ,比如 ceiling,floor,LPAD 等

如果要指定指定范围的随机整数的话，需要用这个公式FLOOR(i + RAND() * j),比如 

# 生成 7 - 11的随机数  SELECT FLOOR(7 + (RAND() * 5));

floor 地板; 取小于该值的最大整数 ,比如 0

mysql select floor(1.23),floor(-1.23);

1           -2 

ceiling 则相反,向上取整,取大于该值的最小整数 ,比如

SELECT CEILING(1.23); # 2

SELECT CEIL(-1.23); # -1

lpad 是左填充, 用法如下 :

LPAD(RAND()*31 + 1,2,'0')) # 取01-31的随机整数 ,保留两位,如果是一位,左边填0

MySQL产生随机数

MYSQL 取随机数

2010年04月26日 星期一 09:48

mysql 取随机数

--对一个表取任意随机数

SELECT *

FROM TMP_XF_TEST

WHERE ID = (SELECT FLOOR(RAND() * (SELECT MAX(ID) FROM TMP_XF_TEST)))

order by id LIMIT 1;

--有条件性的取随机数

SELECT *

FROM TMP_XF_TEST

WHERE ID = (SELECT FLOOR(RAND() *

((SELECT MAX(ID) FROM TMP_XF_TEST WHERE GID = 9) -

(SELECT MIN(ID) FROM TMP_XF_TEST WHERE GID = 9))) +

(SELECT MIN(ID) FROM TMP_XF_TEST WHERE GID = 9))

AND GID = 9

ORDER BY ID LIMIT 1;

--gid上存在索引

或者

SELECT *

FROM TMP_XF_TEST AS t1 JOIN

(SELECT ROUND(RAND() * ((SELECT MAX(id) FROM TMP_XF_TEST WHERE GID = 9)-(SELECT MIN(id) FROM TMP_XF_TEST WHERE GID = 9))

+(SELECT MIN(id) FROM TMP_XF_TEST WHERE GID = 9)) AS id) AS t2

WHERE t1.id = t2.id AND t1.GID = 9

ORDER BY t1.id LIMIT 1;

#########

不要用下面的杯具写法

mysql insert into tmp_xf_test(user_nick,gid,item_id,gmt_create,gmt_modified,memo)

- select user_nick,gid,item_id,gmt_create,gmt_modified,memo from tmp_xf_test;

Query OK, 165888 rows affected (9.65 sec)

Records: 165888 Duplicates: 0 Warnings: 0

mysql SELECT *

- FROM `tmp_xf_test`

- WHERE id = (SELECT FLOOR( MAX(id) * RAND()) FROM `tmp_xf_test` )

- ORDER BY id LIMIT 1;

+-----+-----------+-----+---------+---------------------+---------------------+--------------------+

| id | user_nick | gid | item_id | gmt_create | gmt_modified | memo |

+-----+-----------+-----+---------+---------------------+---------------------+--------------------+

| 467 | 玄风 | 9 | 123 | 2010-04-26 14:56:39 | 2010-04-26 14:56:39 | 玄风测试使用的数据 |

+-----+-----------+-----+---------+---------------------+---------------------+--------------------+

1 row in set (51.12 sec)

mysql explain SELECT *

- FROM `tmp_xf_test`

- WHERE id = (SELECT FLOOR( MAX(id) * RAND()) FROM `tmp_xf_test` )

- ORDER BY id LIMIT 1\G

*************************** 1. row ***************************

id: 1

select_type: PRIMARY

table: tmp_xf_test

type: index

possible_keys: NULL

key: PRIMARY

key_len: 8

ref: NULL

rows: 1

Extra: Using where

*************************** 2. row ***************************

id: 2

select_type: UNCACHEABLE SUBQUERY

table: tmp_xf_test

type: index

possible_keys: NULL

key: idx_tmp_xf_test_gid

key_len: 4

ref: NULL

rows: 331954

Extra: Using index

2 rows in set (0.01 sec)

---

mysql SELECT * FROM `tmp_xf_test` t1 join

- (SELECT FLOOR( MAX(id) * RAND()) as id FROM `tmp_xf_test` ) as t2

- where t1.id =t2.id

- ORDER BY t1.id LIMIT 1;

+-------+-----------+-----+---------+---------------------+---------------------+--------------------+-------+

| id | user_nick | gid | item_id | gmt_create | gmt_modified | memo | id |

+-------+-----------+-----+---------+---------------------+---------------------+--------------------+-------+

| 40311 | 玄风 | 9 | 123 | 2010-04-28 15:47:19 | 2010-04-28 15:47:19 | 玄风测试使用的数据 | 40311 |

+-------+-----------+-----+---------+---------------------+---------------------+--------------------+-------+

1 row in set (0.14 sec)

##############

mysql SELECT * FROM `tmp_xf_test`

- WHERE id = (SELECT floor(RAND() * (SELECT MAX(id) FROM `tmp_xf_test`)))

- ORDER BY id LIMIT 1;

+------+-----------+-----+---------+---------------------+---------------------+--------------------+

| id | user_nick | gid | item_id | gmt_create | gmt_modified | memo |

+------+-----------+-----+---------+---------------------+---------------------+--------------------+

| 1352 | 玄风 | 9 | 123 | 2010-04-28 15:47:19 | 2010-04-28 15:47:19 | 玄风测试使用的数据 |

+------+-----------+-----+---------+---------------------+---------------------+--------------------+

1 row in set (0.00 sec)

mysql explain SELECT * FROM `tmp_xf_test`

- WHERE id = (SELECT floor(RAND() * (SELECT MAX(id) FROM `tmp_xf_test`)))

- ORDER BY id LIMIT 1\G

*************************** 1. row ***************************

id: 1

select_type: PRIMARY

table: tmp_xf_test

type: index

possible_keys: NULL

key: PRIMARY

key_len: 8

ref: NULL

rows: 1

Extra: Using where

*************************** 2. row ***************************

id: 3

select_type: SUBQUERY

table: NULL

type: NULL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: NULL

Extra: Select tables optimized away

2 rows in set, 1 warning (0.00 sec)

对应的另外一种杯具写法是：

SELECT *

FROM TMP_XF_TEST

WHERE ID = (SELECT FLOOR(RAND() * (MAX(ID) - MIN(ID))) + MIN(ID) MID

FROM TMP_XF_TEST

WHERE GID = 9)

AND GID = 9 LIMIT 1;