Create character random names (MySQL extract random Records) and use of MySQL cursors

Recently encountered in the development of some problems, here to record the solution to the method, has been prepared to forget.

1, now create the role of the game, basically support the role of the name of the random, previously this function in the client with code implementation, and then to the server to request and verify, and later found that sometimes a few successive failures, so change to the server implementation. The implementation method mainly consider the use of MySQL random query records, on-line search a lot of programs, and then used in our game.

The implementation is to insert all the random names into a table, and then randomly take a name that does not appear in the current role table.

Begindeclare Randnum int DEFAULT 0; SELECT Floor (RAND () * 309034) to 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, the operation of the demand, sometimes because of server maintenance, need to send some things to all players, this time involves offline players, so our solution is to write the database statically.

Because not in-depth study of MySQL, in the course of practice found that the cursor operation can only modify a data, and later looked up a lot of information, or solve the problem, oneself also learned a little knowledge, modified after 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) &L t;> 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;

There is a SELECT statement in the repeat before the change. This problem arises mainly in the understanding that SQLState is not enough, 02000 anomalies have 3 kinds of conditions:

The result of a subquery a:select into statement or INSERT statement is an empty table.

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

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

It would be nice to put the SELECT statement in the cursor declaration statement later.

Create character random names (MySQL extract random Records) and use of MySQL cursors