MySQL common functions

2024-07-12

As a widely used relational database management system, MySQL provides rich functions to support various types of applications. The following are some common functions of MySQL:

Database Management

1. Create and manage databases and tables:

MySQL provides flexible SQL statements to create, modify, and delete databases and their tables.

CREATE DATABASE mydatabase;
CREATE TABLE mytable (id INT PRIMARY KEY, name VARCHAR(50));
1
2

2. Data insertion, updating and deletion:

Use standard SQL statements to perform data operations.

INSERT INTO mytable (id, name) VALUES (1, 'Alice');
UPDATE mytable SET name = 'Bob' WHERE id = 1;
DELETE FROM mytable WHERE id = 1;
1
2
3

3. Query data:

Data retrieval is performed through SELECT statements, including complex JOINs, subqueries, sorting, and grouping.

SELECT * FROM mytable WHERE name = 'Alice';
SELECT name, COUNT(*) FROM mytable GROUP BY name;
1
2

Security and permissions

1. User management and permission control:

MySQL provides fine-grained permission control, allowing administrators to assign different permissions to different users.

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT ON mydatabase.* TO 'user1'@'localhost';
1
2

2. Role Management:

Starting from MySQL 8.0, role management is supported, which simplifies permission allocation and management.

CREATE ROLE 'developer';
GRANT SELECT, INSERT, UPDATE ON mydatabase.* TO 'developer';
GRANT 'developer' TO 'user1'@'localhost';
1
2
3

Performance Optimization

1. Index:

MySQL supports multiple index types, such as B-Tree index, full-text index, and spatial index, to improve query performance.

CREATE INDEX idx_name ON mytable(name);
1

2. Query cache:

MySQL provides a query cache feature that can cache query results and improve the execution efficiency of the same query (removed in MySQL 8.0).

3. Performance Schema:

Provides a set of tables and tools to monitor and analyze database performance.

SELECT * FROM performance_schema.events_statements_summary_by_digest;
1

Replication and High Availability

1. Master-slave replication:

MySQL supports master-slave replication for data distribution and high availability.

CHANGE MASTER TO MASTER_HOST='master_host', MASTER_USER='replication_user', MASTER_PASSWORD='password';
START SLAVE;
1
2

2. Cluster (MySQL InnoDB Cluster):

Provides highly available database cluster solutions to ensure high availability and consistency of data.

Data backup and recovery

1. Logical backup:

use mysqldump Logical backup tool.

mysqldump -u root -p mydatabase > mydatabase.sql
1

2. Physical backup:

use mysqlbackup(MySQL Enterprise Backup) or xtrabackup(Percona XtraBackup) for physical backup.

Other advanced features

1. Trigger:

SQL code that is automatically executed when a specific event (such as INSERT, UPDATE, DELETE) occurs on a specific table.

CREATE TRIGGER mytrigger BEFORE INSERT ON mytable FOR EACH ROW SET NEW.name = UPPER(NEW.name);
1

2. Stored procedures and functions:

Encapsulate a set of SQL statements so that they can be called multiple times, improving code reusability and maintainability.

CREATE PROCEDURE myprocedure(IN param1 INT)
BEGIN
  SELECT * FROM mytable WHERE id = param1;
END;
1
2
3
4

3. View:

Create virtual tables for complex queries to improve query readability and reuse.

CREATE VIEW myview AS SELECT name FROM mytable WHERE id > 10;
1

Through these features, MySQL provides a powerful toolset to meet the needs of various application scenarios.

Technology Sharing