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 Functionsabs(x)            -- Absolute value abs(-10.9) = 10format(x, d)    -- Format number with thousand separator format(1234567.456, 2) = 1,234,567.46ceil(x)            -- Round up ceil(10.1) = 11floor(x)        -- Round down floor(10.1) = 10round(x)        -- Round to the nearest integermod(m, n)        -- m%n m mod n Remainder 10%3=1pi()            -- Get pipow(m, n)        -- m^nsqrt(x)            -- Square rootrand()            -- Random numbertruncate(x, d)    -- Truncate to d decimal places-- Date and Time Functionsnow(), current_timestamp();     -- Current date and timecurrent_date();                    -- Current datecurrent_time();                    -- Current timedate('yyyy-mm-dd hh:ii:ss');    -- Get the date parttime('yyyy-mm-dd hh:ii:ss');    -- Get the time partdate_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j');    -- Format dateunix_timestamp();                -- Get Unix timestampfrom_unixtime();                -- Convert timestamp to date-- String Functionslength(string)            -- Length of string in byteschar_length(string)        -- Number of characters in stringsubstring(str, position [,length])        -- Substring of str starting at position for length charactersreplace(str, search_str, replace_str)    -- Replace search_str with replace_str in strinstr(string, substring)    -- Position of the first occurrence of substring in stringconcat(string [,...])    -- Concatenate stringscharset(str)            -- Character set of stringlcase(string)            -- Convert to lowercaseleft(string, length)    -- Take length characters from the left of stringload_file(file_name)    -- Load content from a filelocate(substring, string [,start_position])    -- Similar to instr, but can specify start positionlpad(string, length, pad)    -- Pad string on the left with pad until length is reachedltrim(string)            -- Trim leading spacesrepeat(string, count)    -- Repeat string count timesrpad(string, length, pad)    -- Pad string on the right with pad until length is reachedrtrim(string)            -- Trim trailing spacesstrcmp(string1, string2)    -- Compare two strings character by character-- Aggregate Functionscount()sum();max();min();avg();group_concat()-- Other Common Functionsmd5();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 questionA 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 10000Create 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 shopCREATE 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-commitSTART TRANSACTION	-- Start a transactionUPDATE account SET money = money-500 WHERE `name` = 'A';	-- A subtracts 500
UPDATE account SET money = money+500 WHERE `name` = 'B';	-- B adds 500COMMIT;	-- Commit transaction
ROLLBACK;	-- Rollback (only successful before 'commit transaction' is executed)SET autocommit = 1; -- Reset to default