Detailed explanation of primary keys and transactions in MySQL

1. Comments on MySQL primary keys and table fields

1. Primary key and auto-increment

Each table usually has one and only one primary key to indicate the uniqueness of each piece of data.

Features: Values ​​cannot be repeated and cannot be null

Format: create table test (ID int primary key)

Primary key + auto-increment writing method:

Format: create table test (ID int primary key auto_increment)

Note: Auto-increment can only be used with the primary key (if defined separately, an error will be reported)

2. Comments on table fields

mysql> alter table test modify Name varchar(12) comment 'user name';

3. Multi-table query

mysql> create table A(ID int primary key auto_increment,Name varchar(12),Department int);
mysql> create table B(ID int primary key auto_increment,Name varchar(12));
mysql> insert into B(Name) values("Finance"),("Market");
mysql> insert into A(Name,Department) values("张三",1),("李四",2),("王五",2);

mysql> select B.Name 部门,A.Name from B,A where B.ID=2 and A.Department=2;

2. Database Transaction Overview

Database: relational database (supports transactions); non-relational database (does not support)

• What is a transaction : It ensures that batch operations are either fully executed or not executed at all, maintaining data integrity. Simply put, it's either success or failure.

A transaction contains multiple SQL statements, and there are certain relationships between these SQL statements:

• A transaction can be N SQL statements (N >= 0)
• Not all database storage engines support transactions, but the InnoDB storage engine supports transaction processing.

1. Database transaction characteristics (ACID)

2. Transaction concurrency without transaction isolation

• Dirty read: Transaction A reads data modified by uncommitted transaction B. If transaction B fails and rolls back, transaction A reads dirty data.
• Non-repeatable read: In the same transaction, the results of reading the same data are inconsistent (for data).
• Phantom read: In the same transaction, the same query returns different results multiple times (for the total rows in the table).

the difference:

• Dirty reads and non-repeatable reads: Dirty reads are when a transaction reads updated data that has not yet been committed. Non-repeatable read means that the data read several times in the same transaction is different.
• Non-repeatable read and phantom read: Both are in the same transaction. The former means that the data read several times is different, while the latter means that the data read several times is completely different.

Isolation Level:

MySQL supports the above four isolation levels, with repeatable read being the default. If you want to change the isolation level, you need to: sed -i '/\[mysqld]/a transaction-isolation = SERIALIZABLE' /etc/my.cnf

mysql> show variables like '%tx_is%';
mysql> exit
[root@MySQL ~]# sed -i '/\[mysqld]/a transaction-isolation = SERIALIZABLE' /etc/my.cnf
[root@MySQL ~]# systemctl restart mysqld
[root@MySQL ~]# mysql -uroot -p123123 -e "show variables like '%tx_is%';"

4.MySQL database management transactions

Three commands to manage transactions:

• BEGIN: Start a transaction, followed by multiple database operation statements that begin to execute.
• COMMIT: Start to commit a transaction, corresponding to the previous BEGIN operation, and save the results of transaction processing to the data file.
• ROLLBACK: Starts to roll back a transaction. Between BEGIN and COMMIT, all statements in the transaction are undone and the data is restored to the state before BEGIN.
• set autocommit = 0/1: disable or enable autocommit, which is when you exit the MySQL connection program or execute the next DML (Data Manipulation Language) statement.
  

mysql> create table C(ID int);
mysql> insert into C values(1),(2);
mysql> select * from C;
mysql> BEGIN;
mysql> insert into C values(3);
mysql> COMMIT;
mysql> select * from C;

mysql> show variables like 'autocommit'; #Check whether to enable automatic transaction commitmysql> BEGIN;
mysql> insert into C values(4)
mysql> select * from C;
mysql> exit
[root@localhost ~]# mysql -uroot -p123123 -e "select * from Coco.C where ID=4"

set autocommit=0 : Change in the database to take effect temporarily (if you want to change it permanently, you need to modify it sed -i '/\[mysqld]/a autocommit=0' /etc/my.cnf )

mysql> set autocommit=0;
mysql> select * from Coco.C;
mysql> insert into Coco.C values(4);
mysql> select * from Coco.C where ID=4;
[root@localhost ~]# mysql -uroot -p123123 -e "select * from Coco.C where ID=4"

Notice:

• After you start a transaction using BEGIN or START TRANSACTION, autocommit remains disabled until you end the transaction using COMMIT or ROLLBACK.
• Afterwards, the autocommit mode will be restored to its previous state, that is, if autocommit = 1 before BEGIN, autocommit will still be 1 after completing this transaction.
• If autocommit = 0 before BEGIN, autocommit will still be 0 after completing this transaction.
  

1) Alias

mysql> select ID as "Number",Name as "Name",Department as "Department" from A where ID=1;
mysql> select ID "Number",Name "Name",Department "Department" from A where ID=1;

2) Deduplication

mysql> select distinct Department from A;

3) AND and OR operators

AND: logical AND (all conditions must be met); OR: logical OR (only one of the conditions needs to be met).

mysql> select * from A where ID >= 3 and Department = 2;
mysql> select * from A where ID >= 3 or Department = 2;

• in: If there are multiple values ​​for a certain field to be queried, you can use the in keyword.
• between and: between two values, including the numbers on both sides of and.

mysql> select * from A where ID in(1,3,4);
mysql> select * from A where ID not in(1,3,4);
mysql> select * from A where ID between 1 and 3;

4) SQL LIKE Operator

• -_: Represents a single unknown character.
• - %: represents multiple unknown characters.
• For example: A% starts with A, % ends with BB, %C% contains C, _D% the second letter is D, %E__ the third letter from the end is E, %@163.com 163 email address.

mysql> select * from A where Name like "%三%";
mysql> select * from A where Name like "%三%" or Name like "%四";

5) SQL ORDER BY Clause

• asc: ascending order (the default is ascending order)
• desc: descending order.

mysql> select * from A order by ID desc;
mysql> select * from A order by Department,ID desc;

6) Limit Clause

mysql> select * from C;
mysql> select * from C limit 2;
mysql> select * from C limit 0,2;

Summarize

This is the end of this article about MySQL primary keys and transactions. For more relevant MySQL primary keys and transactions, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!