Playing chess within MySQL? Over the network? In the .. cloud? Yes! This is a follow-up post of my ‘A chessboard in MySQL’ where we create and populate a chessboard. But pieces need to move, and a few wondered how. Easy!

As an example, white openes with 1.e4:

sql BEGIN; UPDATE chessboard SET e='♙' WHERE x = 4; UPDATE chessboard SET e='' WHERE x = 2; COMMIT;

Pretty obvious. Now lets put it in a stored procedure (source included in post) so the next move is easier on the fingers and more fun to play. Lets do a 1…e5:

sql CALL move_piece('e','7','e',5);

The result is the following:

shell mysql> SELECT * FROM chessboard; +---+------+------+------+------+------+------+------+------+ | x | a | b | c | d | e | f | g | h | +---+------+------+------+------+------+------+------+------+ | 8 | ♜ | ♞ | ♝ | ♛ | ♚ | ♝ | ♞ | ♜ | | 7 | ♟ | ♟ | ♟ | ♟ | | ♟ | ♟ | ♟ | | 6 | | | | | | | | | | 5 | | | | | ♟ | | | | | 4 | | | | | ♙ | | | | | 3 | | | | | | | | | | 2 | ♙ | ♙ | ♙ | ♙ | | ♙ | ♙ | ♙ | | 1 | ♖ | ♘ | ♗ | ♕ | ♔ | ♗ | ♘ | ♖ | +---+------+------+------+------+------+------+------+------+

Here is the stored procedure. It’s very, very basic, and of course, one can add much more!

```sql DROP PROCEDURE IF EXISTS move_piece; delimiter // CREATE PROCEDURE move_piece( psrcCol CHAR(1), psrcRow TINYINT, pdstCol CHAR(1), pdstRow TINYINT) BEGIN SET @srcCol = psrcCol; SET @srcRow = psrcRow; SET @dstCol = pdstCol; SET @dstRow = pdstRow; SET @piece = 0; SET @blank = ‘’;

-- Get the piece we are moving
SET @stmt = CONCAT('SELECT ',@srcCol,
    '+0 INTO @piece FROM chessboard WHERE x = ?');
PREPARE preStmt FROM @stmt;
EXECUTE preStmt USING @srcRow;
DEALLOCATE PREPARE preStmt;

IF ((@piece > 1 AND @piece <= 14) AND @piece is not NULL)
THEN
    -- Move the piece
    SET @stmt = CONCAT('UPDATE chessboard SET ',
        @dstCol,'=? WHERE x = ?');
    PREPARE preStmt FROM @stmt;
    EXECUTE preStmt USING @piece,@dstRow;
    DEALLOCATE PREPARE preStmt;
    SET @stmt = CONCAT('UPDATE chessboard SET ',
        @srcCol,'=? WHERE x = ?');
    PREPARE preStmt FROM @stmt;
    EXECUTE preStmt USING @blank,@srcRow;
    DEALLOCATE PREPARE preStmt;
ELSE
    SELECT "No piece found at given position." AS Error;
END IF; END; // ```

Some thoughts for future expansion:

  • You could save the moves in a separate table to record time it took.
  • Implement some other movement notation.
  • Lock a player after a move.
  • Build some other stored routines to populate and reset the chessboard.
  • Have multiple chessboard tables.
  • Event scheduler can be used to implement the non-human player!

Anyway, this was all about fun and Unicode testing. There will be probably no follow-up on this post. If somebody is crazy enough to actually implement a chess game in MySQL: awesome!

(Disclaimer: I am not a chess player.)

UPDATE 2009-12-31: Here is the insert statement for populating the chessboard:

sql INSERT INTO `chessboard` VALUES (8,'♜','♞','♝','♛','♚','♝','♞','♜'), (7,'♟','♟','♟','♟','♟','♟','♟','♟'), (6,'','','','','','','',''),(5,'','','','','','','',''), (4,'','','','','','','',''), (3,'','','','','','','',''), (2,'♙','♙','♙','♙','♙','♙','♙','♙'), (1,'♖','♘','♗','♕','♔','♗','♘','♖');