#MySQL database usage tutorial (2024 latest version)

#1. Introduction to MySQL

MySQL is the world's most popular open source relational-database management system (RDBMS). Since its birth in 1995, it has become the database of choice for the vast majority of web application development due to its lightweight, stable and highly compatible characteristics.

For example: If an application is compared to a company, data is the company's assets, and MySQL is like an efficient and secure electronic warehouse, responsible for storing, searching, and updating these assets in an orderly manner.

#Core Features

• High performance, high reliability: After verification in numerous high-concurrency scenarios, the default InnoDB storage engine supports automatic recovery from crashes.
• Cross-platform: Runs seamlessly on Windows, Linux, macOS.
• Flexible storage engine: InnoDB is recommended by default (supports transactions, row-level locks and foreign keys), while MyISAM (suitable for read-only analysis scenarios) is also retained.
• Standard SQL syntax: Follows SQL92, SQL99 and other standards, with low learning cost and easy migration.
• Full Unicode Support: Passedutf8mb4Character set that can store all emoji and rare words.

#MySQL vs SQLite quick comparison

Many students will ask: If you use SQLite for small projects, when should you upgrade to MySQL? The table below will help you clarify your thoughts.

If you are developing an application that requires simultaneous access by multiple users and a growing amount of data, then MySQL is the right choice for you.

#2. MySQL installation and configuration

As of 2024, the officially recommended stable version is MySQL Community Server 8.4 LTS, which provides long-term maintenance for up to 5 years and is very suitable for production environments.

#2.1 Traditional installation (suitable for deep customization)

If you need to install and perform various low-level tuning directly on the operating system, you can do this:

1. Visit MySQL 官方下载中心 and select the installation package corresponding to the platform.
2. Follow the wizard to complete the installation. Be sure to set a strong password (recommendation: uppercase and lowercase letters + numbers + special symbols, length ≥ 8 characters).
3. Remember to adjust the character set settings after installation (see next section).

#2.2 Core configuration optimization

No matter which installation method is used, it is strongly recommended to configure the character set and default storage engine immediately to avoid headaches such as Chinese garbled characters in the future.

• Linux/macOS configuration file path is usually/etc/my.cnfor/etc/mysql/my.cnf
• For Windows, it is in the installation directory.my.inidocument

Add or modify the following content in the configuration file:

[client]
# 客户端连接时默认使用的字符集
default-character-set = utf8mb4

[mysqld]
# 默认存储引擎（必须为 InnoDB）
default-storage-engine = INNODB
# 服务器端字符集
character-set-server = utf8mb4
# 排序规则（unicode_ci 表示大小写不敏感，兼容性好）
collation-server = utf8mb4_unicode_ci

After the modification is completed, restart the MySQL service, then log in to the database and execute the following command to verify:

SHOW VARIABLES LIKE '%char%';

You should see output similar to the following (key items areutf8mb4）：

+--------------------------+--------------------------------------+
| Variable_name            | Value                                |
+--------------------------+--------------------------------------+
| character_set_client     | utf8mb4                              |
| character_set_connection | utf8mb4                              |
| character_set_database   | utf8mb4                              |
| character_set_server     | utf8mb4                              |
+--------------------------+--------------------------------------+

#3. Docker rapid deployment (highly recommended!)

If you don’t want to mess with the environment and hate all kinds of dependency conflicts, then Docker is your savior. Start it with one click and go away after use, and the data can be persisted.

#3.1 One-click start command

Run the following line of commands to start a container with MySQL 8.4 LTS (remember to change the password to your own):

docker run -d \
  --name mysql-local-8.4 \
  -e MYSQL_ROOT_PASSWORD=YOUR_STRONG_PASSWORD_123! \
  -p 3306:3306 \
  -v ./mysql-local-data:/var/lib/mysql \
  --restart=always \
  mysql:8.4 \
  --character-set-server=utf8mb4 \
  --collation-server=utf8mb4_unicode_ci

#3.2 Detailed explanation of parameters

#3.3 Verify deployment

1. Check if the container is running:

   docker ps
2. Enter the container and connect to MySQL using the command line:

   docker exec -it mysql-local-8.4 mysql -u root -p

Enter the password you just set and seemysql>The prompt indicates success!

#4. Python connects to MySQL

In the Python world, we recommend using the official drivermysql-connector-python. It fully follows the Python DB-API 2.0 specification, is stable and has good compatibility.

#4.1 Install driver

pip install mysql-connector-python

#4.2 Complete operation example

The following example covers all core steps from connection, table creation, addition, deletion, modification, transaction submission, and resource release.

import mysql.connector
from mysql.connector import Error

def basic_mysql_operations():
    conn = None
    cursor = None
    try:
        # 1. 建立数据库连接
        conn = mysql.connector.connect(
            host="localhost",
            user="root",
            password="YOUR_STRONG_PASSWORD_123!",
            database="test_db"       # 请事先创建好这个数据库
        )
        if conn.is_connected():
            print("✅ 成功连接 MySQL 数据库")

        # 2. 创建游标，用来执行 SQL 语句
        cursor = conn.cursor()

        # 3. 创建 users 表（如果还不存在）
        create_table_sql = """
        CREATE TABLE IF NOT EXISTS users (
            id VARCHAR(20) PRIMARY KEY,
            name VARCHAR(50) NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        """
        cursor.execute(create_table_sql)
        print("✅ 表 users 创建/检查完成")

        # 4. 插入单条数据（使用 %s 占位符防止 SQL 注入）
        insert_single_sql = "INSERT INTO users (id, name) VALUES (%s, %s)"
        insert_single_value = ("1001", "张三")
        cursor.execute(insert_single_sql, insert_single_value)
        print(f"✅ 插入单条数据，影响行数：{cursor.rowcount}")

        # 5. 批量插入数据（效率远高于循环单条插入）
        insert_batch_sql = "INSERT INTO users (id, name) VALUES (%s, %s)"
        insert_batch_values = [
            ("1002", "李四"),
            ("1003", "王五"),
            ("1004", "赵六")
        ]
        cursor.executemany(insert_batch_sql, insert_batch_values)
        print(f"✅ 批量插入数据，影响行数：{cursor.rowcount}")

        # 6. 提交事务（InnoDB 必须显式提交，否则数据不会真正写入）
        conn.commit()
        print("✅ 事务提交成功")

        # 7. 查询今日新增的用户
        select_sql = "SELECT * FROM users WHERE created_at >= CURDATE()"
        cursor.execute(select_sql)
        results = cursor.fetchall()
        print("\n📋 今日新增的用户：")
        for row in results:
            print(f"ID: {row[0]}, 姓名: {row[1]}, 创建时间: {row[2]}")

    except Error as e:
        print(f"❌ MySQL 操作出错：{e}")
        # 出错时回滚所有未提交的操作，保证数据一致性
        if conn and conn.is_connected():
            conn.rollback()
            print("⚠️ 事务已回滚")
    finally:
        # 8. 正确关闭资源（先关游标，再关连接）
        if cursor and cursor.is_connected():
            cursor.close()
        if conn and conn.is_connected():
            conn.close()
            print("\n🔌 数据库连接已关闭")

if __name__ == "__main__":
    basic_mysql_operations()

#5. Daily development best practices

#5.1 Security aspects

• Don’t use root for everything: Create dedicated users for each application and strictly limit their IP access.
• Strongly use placeholders: Never use string concatenation in SQL.WHERE name = '" + user_input + "'Code like this is a doorway to disaster.
• Encrypted transmission in production environment: Turn on SSL/TLS to prevent data from "streaking" in the network.
• Regular Backup: AvailablemysqldumpScheduled export can also directly back up the data directory mounted by Docker.

#5.2 Performance

• Use connection pool: Database connections are very valuable resources, and repeated establishment and destruction will seriously slow down the application.mysql-connector-pythonComes with connection pool support.
• Reasonable Index Creation: GiveWHERE、JOIN、ORDER BYWith the addition of indexes to the fields involved, the query speed can often change from seconds to milliseconds.
• Batch operation as much as possible: If you can insert 100 pieces of data at a time, don't loop 100 times.executemanyOr inserting multiple rows with a single SQL is a good practice.
• Avoid inefficient queries: Don'tSELECT *out all fields, let alone useLIKE '%keyword'This writing method will lead to a full table scan.

#6. Summary

• Version Selection: New projects can be directly upgraded to MySQL 8.4 LTS for stability and longevity.
• environment-setup: Docker is preferred for development and testing, with fast deployment and clean environment.
• Python operation: Use the official driver, pay attention to Explicitly commit transactions and Release resources in a timely manner.
• Long-Term Development: Adhering to security and performance guidelines from the beginning will allow you to avoid 90% of pitfalls.

#7. Further reading

• MySQL 8.4 官方参考手册
• mysql-connector-python 官方文档
• Docker Hub 上的 MySQL 镜像