Create a role random name (mysql extraction random records) and mysql cursor usage, mysql cursor

Create a role random name (mysql extraction random records) and mysql cursor usage, mysql cursor

I have encountered some problems in development recently. I have forgotten the solution here.

1. Currently, when creating a game role, it basically supports random role names. Previously, this function was implemented on the client using code and then requested and verified from the server, later, it was found that sometimes it failed several times in a row, so it was implemented on the server. The implementation method mainly considers using mysql random query records. I checked a lot of solutions on the Internet and used them in our game.

The solution is to insert all the random names into a table, and then randomly retrieve a name not displayed in the current role table.

BEGINDECLARE randnum int DEFAULT 0;SELECT FLOOR(RAND() * 309034) INTO randnum;SELECT rname INTO _name FROM `names` WHERE ((_sex = sex) AND(rname not in (SELECT Name FROM longwen.player WHERE _world = WorldID)) AND(id > randnum)) LIMIT 1;set returnvalue = 0;END

2. In response to operation requirements, sometimes it is necessary to provide some services to all players due to server maintenance. This involves offline players, so our solution is static database writing.

I have not studied mysql in depth. In practice, I found that the cursor operation can only modify one piece of data. Later I checked a lot of information and solved the problem. I also learned some knowledge, the stored procedure is as follows:

DECLARE _cursor CURSOR FOR SELECT RoleID FROM player WHERE (RoleID NOT IN (SELECT roleID FROM email where LENGTH(datas) <> 0)); DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' set _done = 1;OPEN _cursor;  set _done = 0;REPEATFETCH _cursor into _roleId;replace into email(roleID, datas) values(_roleId, 'test');UNTIL _done END REPEAT;CLOSE _cursor;

The repeat statement contains a select statement before modification. This problem occurs mainly because the SQLSTATE is not fully understood. The 02000 exception has three conditions:

A: The results of the select into statement or the INSERT statement subquery are empty tables.

B: The number of lines identified in the UPDATE or DELETE statement is zero.

C: The cursor referenced in the FETCH statement is placed after the last row of the result table.

Later, I put the select statement into the cursor declaration statement.

There is a mysql DATA table. I want to randomly extract 10 records. What is the best method?

1. Use MYSQL built-in functions. The specific SQL code is as follows:
SELECT * FROM tablename order by rand () LIMIT 10
2. Do not assign a large amount of work to the database. This will cause the database to be locked and blocked within a certain set of concurrent times.
We recommend that you use PHP to randomly generate numbers between (total number of rows) and use these 10 random numbers as the query conditions. The specific statement is as follows:
SELECT * FROM tablename where ID in (, 33)
You may also need to repeat the exclusion, and you need to concatenate 10 values in the program and connect them into the SQL statement.

Mysql randomly extracts multiple different records from the data table.

Mysql uses order by rand () for random acquisition

Mysql> select * FROM sale_report LIMIT 0, 10;
+ --------------------- + ----------- + ------------ +
| SALE_DATE | SALE_ITEM | SALE_MONEY |
+ --------------------- + ----------- + ------------ +
| 00:00:00 | A | 2009.00 |
| 00:00:00 | B | 1.00 |
| 00:00:00 | C | 1.00 |
| 00:00:00 | A | 2009.00 |
| 00:00:00 | B | 1.00 |
| 00:00:00 | C | 2.00 |
| 00:00:00 | A | 2009.00 |
| 00:00:00 | B | 1.00 |
| 00:00:00 | C | 3.00 |
| 00:00:00 | A | 2009.00 |
+ --------------------- + ----------- + ------------ +
10 rows in set (0.00 sec)

Mysql> select * FROM sale_report order by rand () LIMIT 0, 10;
+ --------------------- + ----------- + ------------ +
| SALE_DATE | SALE_ITEM | SALE_MONEY |
+ --------------------- + ----------- + ------------ +
| 00:00:00 | A | 2009.00 |
| 00:00:00 | A | 2009.00 |
| 00:00:00 | C | 20.00 |
| 00:00:00 | C | 14.00 |
| 00:00:00 | A | 2009.00 |
| 00:00:00 | B | 2.00 |
| 00:00:00 | A | 2009.00 |
| 00:00:00 | C | 20.00 |
| 00:00:00 | A | 2009.00 |
| 00:00:00 | B | 7. 00 ...... remaining full text>