MySQL Tips

关系型数据库的理想存储量1GB

1. Create Table

CREATE TABLE user (
                         user_id                      int NOT NULL auto_increment,
                         username                  varchar(20) NOT NULL,
                         firstname                   varchar(20) NOT NULL,
                         lastname                   varchar(20) NOT NULL,
                         birth_date                  Date,
                         latest_update_date   Date,
                         PRIMARY KEY (user_id),
                         UNIQUE(username)
                     );

CREATE TABLE video (
                         video_id                    int NOT NULL auto_increment,
                         author_id                   int NOT NULL,
                         title                            varchar(50) NOT NULL,
                         description                varchar(255),
                         star                            int NOT NULL,
                         heart                          int NOT NULL,
                         upload_date              Date,
                         latest_update_date   Date,
                         PRIMARY KEY (video_id),
                         FOREIGN KEY (author_id) REFERENCES user(user_id),
                         INDEX (start, heart)
                     );

2. Load data from file to database
   Remember to set the --infile = 1 when log into the database

3. Insert data from a table into another table (can also include constant)
   In my MySQL Ver 8.0.15, a char/varchar type must be quoted in " but not '.

INSERT INTO user (username, firstName, lastName, birthDate, lastest_update_date) SELECT username, firstName, lastName, STR_TO_DATE(birthDate,"%Y-%m-%d"), DATE(now()) FROM user_raw;

4. Copy Table

CREATE TABLE targetTable LIKE sourceTable;
INSERT INTO targetTable SELECT * FROM sourceTable;

5. Join
   Left join and right join can help to avoid the error caused by NULL fields.

SELECT user_id, username, video_id, description FROM user LEFT JOIN video ON user_id = author_id;

6. Find the Nth largest
   LIMIT N, M -- skip N tuples and then choose M tuples
   LIMIT M OFFSET N -- skip N tuples and then choose M tuples
   IFNULL(A,B) -- If A is NULL, return B. Otherwise, return A.

# Find 3rd highest salary
SELECT IFNULL((SELECT distinct Salary FROM Employee ORDER BY Salary desc Limit 1 OFFSET 2),NULL) as SecondHighestSalary
SELECT IFNULL((SELECT distinct Salary FROM Employee ORDER BY Salary desc Limit 2,1),NULL) as SecondHighestSalary

7. Add, remove, rename columns

#Add column
ALTER TABLE testu ADD COLUMN tmp DATE;
#Remove column
ALTER TABLE testu DROP birth_date;
#Rename column
ALTER TABLE testu CHANGE tmp birth_date DATE;

8. DATE, DATETIME, TIMESTAMP Conversion

// DATETIME to DATE
mysql> SELECT NOW() AS 'datetime', CONVERT ( NOW(), date ) AS 'date', 

// DATETIME to TIMESTAMP
mysql> SELECT NOW() AS 'datetime', UNIX_TIMESTAMP(NOW()) AS 'timestamp';

// DATE to TIMESTAMP
mysql> SELECT birth_date AS 'date', UNIX_TIMESTAMP(birth_date) AS 'timestamp' FROM testu;;

// TIMESTAMP to DATETIME/DATE
mysql> SELECT FROM_UNIXTIME(1545711900);
mysql> UPDATE testu SET birth_date = FROM_UNIXTIME(1173888000) WHERE id = 43;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

9. 查看版本的状态信息

mysql> status

10. 改变表格自增值

ALTER TABLE testu auto_increment=1000;

11. 查看表格基本属性

 SHOW TABLE STATUS;

其中有一项auto_increment表示了表格的自增值

12. 替换String
    在修改文件存储地址的时候很有用

UPDATE resource SET path=REPLACE(path, 'https://my-new-cdn.example.com/', 'https://my-cdn.example.com/sit/');

13. PROCEDURE & FUNCTION
    很相似的多行SQL语句的集合
    Procedure里可以有事务，常用来定义复杂业务流程并实现失败回滚

# 比如要彻底删除一个用户，则要同时删除和该用户绑定的一系列任务参数
DELIMITER $$

CREATE PROCEDURE delete_user (IN uid bigint)
BEGIN
    DECLARE `_rollback` BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
    START TRANSACTION;
    DELETE FROM task_result WHERE task_id IN (SELECT t_id FROM task WHERE user_id = uid);
    DELETE FROM task_execution_stage WHERE task_id IN (SELECT t_id FROM task WHERE user_id = uid);
    DELETE FROM operation_attributes WHERE task_operation_id IN (SELECT to_id FROM task_operation WHERE task_id IN (SELECT t_id FROM task WHERE user_id = uid));
    DELETE FROM task_operation WHERE task_id IN (SELECT t_id FROM task WHERE user_id = uid);
    DELETE FROM task WHERE user_id = uid;
    DELETE FROM user_api_credential WHERE user_id = uid;
    DELETE FROM user WHERE id = uid;
    IF `_rollback` THEN
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;
    END$$

DELIMITER ;

查看所有Procedure

SHOW PROCEDURE STATUS;
SHOW PROCEDURE STATUS WHERE db = 'classicmodels'; # 查看某一数据库的存储过程

查看Procedure的具体实现

SHOW CREATE PROCEDURE <stored_procedure_name>

Function里如果带有事务的Commit语句则会报错

[2021-12-23 13:46:43] [HY000][1422] Explicit or implicit commit is not allowed in stored function or trigger.

调用

CALL delete_user(31);

删除

DROP PROCEDURE delete_user;

想要更新的话需要先删除再重新添加

14. 命令行启动/停止/重启mysql服务器

$ sudo /usr/local/mysql/support-files/mysql.server start
$ sudo /usr/local/mysql/support-files/mysql.server stop
$ sudo /usr/local/mysql/support-files/mysql.server restart

15. 查看数据库与表格使用的引擎或字符集

SHOW CREATE DATABASE my_db; //查看库的字符集
SHOW CREATE TABLE my_db.my_table; //查看表的字符集
SHOW FULL COLUMNS FROM my_db.my_table; //查看字段编码

修改库的字符集

ALTER DATABASE my_db DEFAULT CHARACTER SET utf8; 
ALTER DATABASE my_db DEFAULT CHARACTER SET utf8mb4; 

修改表以及字符字段的字符集

ALTER TABLE kepler.statis CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE kepler.statis CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;