使用存储过程在MySQL中随机播放表 [英] Shuffle a table in MySQL with a Stored Procedure

问题描述

我想在MySQL中创建一个可以重新整理表中所有记录的过程.到目前为止，这就是我现在所拥有的.

I want to create a procedure in MySQL that can shuffle all my records inside a table. So far this is what i have now.

/*
    SHUFFLE A TABLE PROCEDURE
*/

DELIMITER $$

USE `nologinsdb`$$

DROP PROCEDURE IF EXISTS `shuffle_record_table`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `shuffle_record_table`()
BEGIN
    DROP TABLE IF EXISTS record_table2;

    CREATE TABLE record_table2 LIKE record_table;

    INSERT INTO record_table2 SELECT * FROM record_table ORDER BY RAND();

    DROP TABLE record_table;

    RENAME TABLE record_table2 TO record_table;
END$$

DELIMITER ;


CALL shuffle_record_table();

我听不懂

SELECT * FROM record_table ORDER BY RAND()

给我随机的顺序，但是当我插入record_table2表时，它的顺序与record_table表相同.

gives me random order, but when i inserting in the record_table2 table it has the same order as the record_table table.

我不明白.

谢谢.

推荐答案

此处

Here http://dev.mysql.com/doc/refman/5.1/en/create-table.html
they say that:

使用LIKE根据另一个表的定义创建一个空表，包括在原始表中定义的任何列属性和索引:

这意味着还将主键也复制到新表中，并保留行顺序.
只需将主键放在新表中:

It means, that a primary key is also copied to a new table, and preservers the rows order.
Just drop the primary key in the new table :

CREATE TABLE record_table2 LIKE record_table;
ALTER TABLE record_table2 DROP PRIMARY KEY;
INSERT INTO record_table2 SELECT * FROM record_table ORDER BY RAND();

-----编辑-----

如果上述解决方案由于主键列上有一个auto_icrement而无法解决，请
然后尝试CREATE TABLE x AS SELECT * FROM y，这不会复制索引和键:

----- EDIT -----

If the above solution doesn't work since there is an auto_icrement on the primary key column,
then try CREATE TABLE x AS SELECT * FROM y, this doesn't copy indexes and keys:

create table x( 
  x int primary key auto_increment,
  y int );

insert into x( y ) values( 1 ), (3 ), (2),(5),(4);

select * from x;
+ ------ + ------ +
| x      | y      |
+ ------ + ------ +
| 1      | 1      |
| 2      | 3      |
| 3      | 2      |
| 4      | 5      |
| 5      | 4      |
+ ------ + ------ +

5 rows

create table y as select * from x order by rand();

select * from y;

+ ------ + ------ +
| x      | y      |
+ ------ + ------ +
| 2      | 3      |
| 4      | 5      |
| 3      | 2      |
| 1      | 1      |
| 5      | 4      |
+ ------ + ------ +

5 rows

这篇关于使用存储过程在MySQL中随机播放表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！