当前位置：网站首页>MySQL Advanced Commands

MySQL Advanced Commands

2022-08-10 22:03:00 【InfoQ】

Advanced Instructions

Indexing

Index Type

-- query index
mysql> SHOW INDEX FROM student;

-- create index
mysql> CREATE [UNIQUE|FULLTEXT] INDEX idx_student_age 
 -> [USING BTREE] -- Specify the index type, default B+ tree
 -> ON student(age); -- Specify the index attribute

mysql> ALTER TABLE student ADD INDEX [idx_student_age](id,age); 
mysql> ALTER TABLE student ADD UNIQUE [uniq_student_age](age); 
mysql> ALTER TABLE student ADD FULLTEXE [ft_student_age](age); 

-- deleteIndex
mysql> DROP INDEX idx_student_age ON student;

mysql> ALTER TABLE student DROP INDEX idx_student_age; Copy to clipboardErrorCopied

View

View Algorithm

Update Options

--Create View
mysql> CREATE VIEWview_student
 -> AS (SELECT * FROM student);

mysql> CREATE ALGORITHM = MERGE
 -> VIEW view_student
 -> AS (SELECT * FROM student)
 -> WITH LOCAL CHECK OPTION; 

-- view structure
mysql> SHOW CREATE VIEW view_student;

-- Delete view
mysql> DROP VIEW [IF EXISTS] view_student;

-- Modify view structure (use with caution)
mysql> ALTER VIEW view_student
 -> AS (SELECT * FROM student);Copy to clipboardErrorCopied

Transaction

After the transaction is opened, all entered SQL statements will be considered as aThe whole of the division is executed uniformly at the time of submission.

If there is a problem during the input process, you can manually roll back.Savepoints can be set during import.

-- Transaction Start
mysql> START TRANSACTION;
mysql> BEGIN;
-- Transaction Commit
mysql> COMMIT;
-- transaction rollback
mysql> ROLLBACK;

-- savepoint
mysql> SAVEPOINT mypoint; -- set savepoint
mysql> ROLLBACK TO SAVEPOINT mypoint; -- Rollback to savepoint
mysql> RELEASE SAVEPOINT mypoint; -- Delete savepoint Copy to clipboardErrorCopied

The InnoDB storage engine supports turning off auto-commit and forcibly turning it onTransaction: Any operation must be committed to COMMIT to persist data, otherwise it will not be visible to other clients.

mysql> SET AUTOCOMMIT = 0|1; -- 0 means turn off auto-commit, 1 means turn on auto-commit.Copy to clipboardErrorCopied

Locks

MySQL can manually lock tables/rows to prevent improper reads and writes by other clients.

--lock
mysql> LOCK TABLES student [AS alias]; 
--unlock
mysql> UNLOCK TABLES;Copy to clipboardErrorCopied

Trigger

The trigger is the database object related to the table, which monitors the addition, modification and deletion of records.When a specific event occurs, the object will be activated to execute the SQL statement.

MySQL database only supports
row-level triggers
: if an INSERT statement inserts N rows of data, the statement-level triggerTriggers execute only once, row-level triggers execute N times.

In a trigger, you can use
OLD
and
NEW
represent the old and new data for the row.The delete operation is only
OLD
, and the add operation is only
NEW
.

-- view triggers
mysql> SHOW TRIGGERS;

-- create triggers
mysql> CREATE TRIGGER my_trigger 
 -> BEFORE INSERT -- Trigger time BEFORE/AFTER Trigger condition INSERT/UPDATE/DELETE
 -> ON student -- The listening table must be a permanent table
 -> FOREACH ROW -- row level trigger
 -> BEGIN
 -> INSERT INTO student_logs(id,op,op_time,op_id) VALUES(null,'insert',now(),new.id)
 -> END;

-- delete trigger
mysql> DROP TRIGGER [schema_name.]trigger_name;

原网站

版权声明
本文为[InfoQ]所创，转载请带上原文链接，感谢
https://yzsam.com/2022/222/202208102111155126.html