0428-mysql (Transactions, permissions)

1. Business

A "transaction" is a mechanism that guarantees that "multiple statements can be executed at once" or "none".

Two ways to start a transaction:

　1.set autocommit = 0; False, the statement is no longer a transaction and must be valid with a commit statement. Default =1 (True)

2.start Transaction//DECLARE transaction start.

The basic implementation process of a transaction:

1. Declaring the transaction started: Starttransaction;

2. Set a number of specific statements to execute, such as:insert, update, Delete, select... In fact, execution, just the statements that are executed, do not "take effect"-it is only execution in the memory state, not the execution of the physical state.

3. Determine if the statements need to be executed:

If (judging if there is an error){

Enforcement – Only for the physical changes that have been made-that is, effective.

Commit

}

else{

Rollback--no statements are executed.

Rollback

}

Judging whether there are errors is usually divided between the two environments:

1. If it is cmd, directly observe if there is an error.

2. In a php program, you need to use the mysql_error () function to determine if there is an error.

2. Permissions

MySQL default has a root user, but this user rights are too large, usually only in the management of the database. If you are connecting to a MySQL database in your project, it is recommended that you create a new user with a smaller permission to connect.

You can create a new user for MySQL by entering the following command in MySQL command line mode:

1	CREATEUSER username IDENTIFIED BY‘password‘;

The new user is created, but at this point, if logged in as this user, will be an error, because we have not assigned the appropriate permissions for this user, the command to assign permissions are as follows:

1	GRANTALL PRIVILEGES ON *.* TO ‘username‘@‘localhost‘ IDENTIFIED BY‘password‘;

Grant all permissions for the username user on all databases.

If you find that the permission you just gave is too large, if we just want to grant it permissions on a database, then we need to switch to the root user to revoke the permissions just now and re-authorize:

12	evoke all privileges on *.* from ' username ' @ ' localhost ' grant all privileges on wordpress.* to ' username ' @ ' localhost ' identified by ' password '

You can even specify that the user can only perform select and UPDATE commands:

1	GRANTSELECT, UPDATE ON wordpress.* TO ‘username‘@‘localhost‘ IDENTIFIED BY‘password‘;

In this way, to log in to MySQL again with username, only the WordPress database is visible to it, and if you only grant it SELECT permission, then it cannot execute the DELETE statement.

In addition, each time you adjust permissions, you typically need to perform the following statement refresh permissions:

1	FLUSH PRIVILEGES;

Delete the user you just created:

1	DROPUSER[email protected];

With the above commands in mind, you can find that the host of the response (that is, the content after the @ symbol) is specified in either authorization or revocation, as the above pass command is actually the user table in the MySQL database and can be viewed with the following command

1 SELECT User  from user;

0428-mysql (transactions, permissions)