2024.4.7 Sunday
Following the previous article 【WEEK6】 【DAY3】MySQL Functions【English Version】
Contents
- 5.3. MD5 Encryption
- 5.3.1. Introduction
- 5.3.2. Testing MD5 Encryption
- 5.3.2.1. Plain Text Passwords
- 5.3.2.2. Implementing Data Encryption
- 5.3.2.3. Encryption for ID 1
- 5.3.2.4. Encrypt All Passwords
- 5.3.2.5. Encryption Upon Insertion
- 5.3.2.6. Verifying Encrypted Statements
- 5.4. Summary
- 6. Transactions and Indexes
- 6.1. What is a Transaction
- 6.2. The ACID Principles of Transactions
- 6.2.1. Atomicity
- 6.2.2. Consistency: Eventual Consistency (Conservation of Energy)
- 6.2.3. Isolation
- 6.2.4.Durability
- 6.2.5. Problems Caused by Isolation
- 6.3. Basic Syntax
- 6.4. Simulated Scenario
- 6.4.1. Create Table, Insert Data
- 6.4.2. Simulate Transfer
5.3. MD5 Encryption
5.3.1. Introduction
MD5, standing for Message-Digest Algorithm 5, is used to ensure information transmission is complete and consistent. It is one of the widely used cryptographic hash functions in computing (also known as digest algorithms or hash algorithms), with mainstream programming languages commonly having MD5 implementations. It operates data (such as Chinese characters) to another fixed-length value, which is the basic principle of hash functions. MD5 has predecessors including MD2, MD3, and MD4.
Irreversible.
5.3.2. Testing MD5 Encryption
(Create a table first)
-- MD5 --
-- Testing MD5 Encryption
CREATE TABLE `testmd5`(
`id` INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8
5.3.2.1. Plain Text Passwords
-- Plain Text Passwords
INSERT INTO testmd5 VALUES (1, 'ZHANGSAN', '123456'),(2, 'LISI', '123456'),(3, 'WANGWU', '123456')
5.3.2.2. Implementing Data Encryption
5.3.2.3. Encryption for ID 1
-- Encryption
UPDATE testmd5 SET pwd = MD5(pwd) WHERE id = 1
5.3.2.4. Encrypt All Passwords
-- Encrypt All Passwords
UPDATE testmd5 SET pwd = MD5(pwd)
5.3.2.5. Encryption Upon Insertion
-- Encryption Upon Insertion
INSERT INTO testmd5 VALUES (4, 'xiaoming', MD5('123456'))
5.3.2.6. Verifying Encrypted Statements
-- How to verify: Encrypt the password provided by the user with MD5, then compare it with the encrypted value (when the same value is encrypted the same number of times, the resulting encryption result is exactly the same)
SELECT * FROM testmd5 WHERE `name` = 'xiaoming' AND pwd = MD5('123456')
5.4. Summary
-- ================ Built-in Functions ================
-- Numeric Functions
abs(x) -- Absolute value abs(-10.9) = 10
format(x, d) -- Format number with thousand separator format(1234567.456, 2) = 1,234,567.46
ceil(x) -- Round up ceil(10.1) = 11
floor(x) -- Round down floor(10.1) = 10
round(x) -- Round to the nearest integer
mod(m, n) -- m%n m mod n Remainder 10%3=1
pi() -- Get pi
pow(m, n) -- m^n
sqrt(x) -- Square root
rand() -- Random number
truncate(x, d) -- Truncate to d decimal places
-- Date and Time Functions
now(), current_timestamp(); -- Current date and time
current_date(); -- Current date
current_time(); -- Current time
date('yyyy-mm-dd hh:ii:ss'); -- Get the date part
time('yyyy-mm-dd hh:ii:ss'); -- Get the time part
date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j'); -- Format date
unix_timestamp(); -- Get Unix timestamp
from_unixtime(); -- Convert timestamp to date
-- String Functions
length(string) -- Length of string in bytes
char_length(string) -- Number of characters in string
substring(str, position [,length]) -- Substring of str starting at position for length characters
replace(str, search_str, replace_str) -- Replace search_str with replace_str in str
instr(string, substring) -- Position of the first occurrence of substring in string
concat(string [,...]) -- Concatenate strings
charset(str) -- Character set of string
lcase(string) -- Convert to lowercase
left(string, length) -- Take length characters from the left of string
load_file(file_name) -- Load content from a file
locate(substring, string [,start_position]) -- Similar to instr, but can specify start position
lpad(string, length, pad) -- Pad string on the left with pad until length is reached
ltrim(string) -- Trim leading spaces
repeat(string, count) -- Repeat string count times
rpad(string, length, pad) -- Pad string on the right with pad until length is reached
rtrim(string) -- Trim trailing spaces
strcmp(string1, string2) -- Compare two strings character by character
-- Aggregate Functions
count()
sum();
max();
min();
avg();
group_concat()
-- Other Common Functions
md5();
default();
6. Transactions and Indexes
6.1. What is a Transaction
6.1.1. A transaction is a group of SQL statements that are executed together.
6.1.2. If one SQL statement within the group fails, all SQL statements in that batch are cancelled.
6.1.3. MySQL transaction processing only supports the InnoDB and BDB table types.
6.2. The ACID Principles of Transactions
https://www.jianshu.com/p/133d8b798271
6.2.1. Atomicity
All operations within the entire transaction either complete fully or are completely undone. They do not stop at any intermediate point. If an error occurs during the execution of the transaction, it will be rolled back to the state before the transaction started, as if the transaction had never been executed.
6.2.2. Consistency: Eventual Consistency (Conservation of Energy)
A transaction can encapsulate state changes (unless it is read-only). The system must always remain consistent, no matter how many concurrent transactions there are at any given time. That is, even if there are multiple concurrent transactions, the system must operate as if transactions were serial. Its main features are protectiveness and invariance, using the transfer example, assume there are five accounts, each with a balance of 100 units, then the total of the five accounts is 500 units. If multiple transfers occur among these 5 accounts at the same time, no matter how many concurrent ones, for example, transferring 5 units between A and B, 10 units between C and D, and 15 units between B and E, the total of the five accounts should still be 500 units. This is protectiveness and invariance.
6.2.3. Isolation
Execute transactions in isolation, making them appear as the only operation in the system at a given time. If there are two transactions, running at the same time, performing the same functions, the isolation of the transactions ensures that each transaction is considered by the system to be the only one using the system. This property is sometimes referred to as serializability. To prevent confusion between transaction operations, requests must be serialized or sequenced so that only one request is made on the same data at the same time.
6.2.4.Durability
After the transaction is completed (committed), the changes made by the transaction to the database are permanently saved in the database and will not be rolled back.
6.2.5. Problems Caused by Isolation
Dirty read: Reading uncommitted data from another transaction.
Non-repeatable reads: Reading a row of data from a table and getting different results at different times within a transaction. (This is not necessarily wrong, just inappropriate in some cases)
Phantom reads: Reading data inserted by another transaction within a transaction, leading to inconsistency in the total amount read before and after. (Usually row-affected, e.g., an additional row)
6.3. Basic Syntax
-- Transactions --
-- MySQL transactions are set to auto-commit by default
SET autocommit = 0 -- Disable
SET autocommit = 1 -- Enable (default)
-- Manually handling transactions (first, disable auto-commit)
SET autocommit = 0
-- Start of transaction
START TRANSACTION -- Marks the start of a transaction, from this line forward all SQL are in the same transaction
INSERT XX
INSERT XX
-- (If successful) commit: persist changes
COMMIT
-- (If unsuccessful) rollback: revert to original state
ROLLBACK
-- End of transaction (then re-enable auto-commit)
SET autocommit = 1
-- Savepoints
SAVEPOINT savepoint_name -- Sets a savepoint within a transaction
ROLLBACK TO SAVEPOINT savepoint_name -- Offers a chance to rollback to a previous savepoint
RELEASE SAVEPOINT savepoint_name -- Removes a savepoint
6.4. Simulated Scenario
/*
Class test question
A purchases a product priced at 500 units online, paying via bank transfer.
A's bank card balance is 2000, then pays 500 to merchant B.
Merchant B's initial bank card balance is 10000
Create shop database and account table and insert 2 records
*/
6.4.1. Create Table, Insert Data
#Simulated scenario
-- Transferring funds
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop
CREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8
-- Initialize (insert) relevant data
INSERT INTO account(`name`, `money`)
VALUES ('A', 2000.00),('B', 1000.00)
6.4.2. Simulate Transfer
-- Simulating transfer: Transactions (execute in batches separated by blank lines)
SET autocommit = 0; -- Disable auto-commit
START TRANSACTION -- Start a transaction
UPDATE account SET money = money-500 WHERE `name` = 'A'; -- A subtracts 500
UPDATE account SET money = money+500 WHERE `name` = 'B'; -- B adds 500
COMMIT; -- Commit transaction
ROLLBACK; -- Rollback (only successful before 'commit transaction' is executed)
SET autocommit = 1; -- Reset to default