MySQL Subversion practical notes (ii)--User login (a): The magical use of unique indexes

Copyright NOTICE: NOTE-taker fugitive pawns love freedom, advocating sharing. But this note stems from the www.jtthink.com (programmer in the awkward way) Judging teacher's "web-level MySQL subversive combat course." For reprint please respect the teacher Labor, retain the judging teacher attribution and course source address.

First, we use the stored procedure of the last lesson to press 1 million data (each 1 million) on the two tables.

The first table User_sys we are using the InnoDB mode, the pawn own insert result is:

　　

　　The second table User_sys2 we are using the MyISAM mode, the pawn own insert result is:

　　　　

　The efficiency of the two engines varies significantly, so we re-use the selection according to the actual situation. It is necessary to choose the innodb mode without transaction and the use of MyISAM mode for table lock, which greatly improves the efficiency of data processing.

Second, select when the MyISAM significantly faster than InnoDB.

Select Count (* from User_sys;

1

Select Count (* from User_sys2;

Three, let's now build an index.

Select Library Jtthink in Navicat, and then select Design Table User_sys.

　　 　Here are two simple concepts:

Normal index--normal: the most basic type of index, and it does not have a limit such as uniqueness.

Uniqueness Index--unique: This index is basically the same as the previous "normal index", but there is one difference: all the values of the indexed column can only occur once, that is, they must be unique.

　

Iv. Let's implement the first function point-user login.

Write a stored procedure
1, determine whether the user name and password match, if the match will return the row of data, if not match returns an error row
2, regardless of success is not successful record a log.
　　

First, let's start by creating a log table. The field properties are as follows:

Table Name: User_log

ID int self-increment
Log_type varchar (20) type, temporarily put some strings
Log_date Timestamp default value is Current_timestamp

　　

and then we'll write this stored procedure sp_user_login:

1 BEGIN2     Set @gid=0;3     Set @user_name="';4     Set @_result='Login sucess';5     SELECTIduser_name  into @gid,@user_name  fromUser_sysWHERE user_name=_user_name  andUser_pwd=_user_pwd LIMIT 1;6     7     IF @gid=0  Then#登录不成功8         SET @_result='Login Error';9     END IF; #不管是否匹配成功, we all return one row of data. And the first field of the row represents the state of the execution result set. Ten      One     SELECT *  from(SELECT @_result

  as_result) A, (SELECT @gid,@user_name) b; A          -      - END

　　Then, let's call Sp_user_login (' Casual username ', ' random password ');

　　

　　Then the pawn is tested by inserting the correct data.

　　

　　

Well, we're done with this lesson.

MySQL Subversion practical notes (ii)--User login (a): The magical use of unique indexes