56,677
社区成员
发帖
与我相关
我的任务
分享
DROP TABLE IF EXISTS `guestcount`;
CREATE TABLE `guestcount` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`ActID` varchar(64) DEFAULT NULL,
`ActPwd` varchar(64) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`),
UNIQUE KEY `AccountNameIndex` (`ActID`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8;
DROP PROCEDURE IF EXISTS `spAccount_QueryAccount`;
DELIMITER ;;
CREATE PROCEDURE `spAccount_QueryAccount`(IN player_name VARCHAR(64),IN player_password VARCHAR(64))
BEGIN
DECLARE playerHave INT;
IF player_name!='' THEN
SELECT COUNT(ActID) INTO playerHave FROM guestcount WHERE ActID = player_name;
IF playerHave = 0 THEN
INSERT INTO guestcount(ActID, ActPwd) VALUES(player_name, player_password);
IF LAST_INSERT_ID()<1000001 THEN
UPDATE guestcount SET ID=1000001 WHERE ID=LAST_INSERT_ID();
SELECT * FROM guestcount WHERE ID = 1000001;
ELSE
SELECT * FROM guestcount WHERE ID = LAST_INSERT_ID();
END IF;
ELSE
SELECT * FROM guestcount WHERE ActID = player_name AND ActPwd = player_password;
END IF;
ELSE
INSERT INTO guestcount(ActID,ActPwd) VALUES(LAST_INSERT_ID() + 1, LAST_INSERT_ID() + 1);
SELECT * FROM guestcount WHERE ID = LAST_INSERT_ID();
END IF;
END
;;
DELIMITER ;
Database changed
mysql> set profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> call spAccount_QueryAccount('lxl2002','lxl2002');
+---------+---------+---------+---------------------+
| ID | ActID | ActPwd | create_time |
+---------+---------+---------+---------------------+
| 1590917 | lxl2002 | lxl2002 | 2018-08-03 15:32:31 |
+---------+---------+---------+---------------------+
1 row in set (0.62 sec)
Query OK, 0 rows affected (0.62 sec)
mysql> SELECT * FROM guestcount WHERE ActID = 'lxl2002' AND ActPwd = 'lxl2002';
+---------+---------+---------+---------------------+
| ID | ActID | ActPwd | create_time |
+---------+---------+---------+---------------------+
| 1590917 | lxl2002 | lxl2002 | 2018-08-03 15:32:31 |
+---------+---------+---------+---------------------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(ActID) FROM guestcount WHERE ActID = 'lxl2002';
+--------------+
| COUNT(ActID) |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
mysql> show profiles;
+----------+------------+---------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------------------------------------+
| 1 | 0.26112300 | SELECT COUNT(ActID) INTO playerHave FROM guestcount WHERE ActID = player_name |
| 2 | 0.35332525 | SELECT * FROM guestcount WHERE ActID = player_name AND ActPwd = player_password |
| 3 | 0.00020225 | SELECT * FROM guestcount WHERE ActID = 'lxl2002' AND ActPwd = 'lxl2002' |
| 4 | 0.00017550 | SELECT COUNT(ActID) FROM guestcount WHERE ActID = 'lxl2002' |
+----------+------------+---------------------------------------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)
mysql>
ELSE
EXPLAIN SELECT * FROM guestcount WHERE ActID = player_name AND ActPwd = player_password;
SHOW WARNINGS;
END IF;
mysql> show profiles;
+----------+------------+---------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------------------------------------+
| 1 | 0.26112300 | SELECT COUNT(ActID) INTO playerHave FROM guestcount WHERE ActID = player_name |
| 2 | 0.35332525 | SELECT * FROM guestcount WHERE ActID = player_name AND ActPwd = player_password |
| 3 | 0.00020225 | SELECT * FROM guestcount WHERE ActID = 'lxl2002' AND ActPwd = 'lxl2002' |
| 4 | 0.00017550 | SELECT COUNT(ActID) FROM guestcount WHERE ActID = 'lxl2002' |
+----------+------------+---------------------------------------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)