2024.4.7 Sunday
Following the previous article 【WEEK6】 【DAY3】MySQL Functions【English Version】

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