MySQL 实战：小型项目中的数据库应用（一）-EW帮帮网

微信交流群：https://732903.hlcode.cn/?id=NK1fWUR

MySQL 简介与小型项目适配性分析

MySQL 是一个开源的关系型数据库管理系统，由瑞典 MySQL AB 公司开发，现属于 Oracle 公司。它在 Web 应用方面被广泛使用，也是一种关联数据库管理系统，能将数据保存在不同的表中，以此增加速度并提高灵活性。其使用的 SQL 语言是用于访问数据库的最常用标准化语言，并且软件采用了双授权政策，分为社区版和商业版。

MySQL 之所以在全世界得到广泛认可和应用，主要得益于它的三大特性：一是成本低，可以免费使用和修改；二是性能强，处理速度快；三是简单实用，对初学者友好。一般中小型网站的开发都会选择 MySQL 作为网站数据库。

对于小型项目来说，MySQL 是一个非常合适的选择，这主要归因于以下几个方面：

成熟和稳定

MySQL 拥有多年的开发历史，经过了长时间的实践检验和不断完善，已经积累了广泛的用户基础。在实际应用中，它展现出了很高的稳定性，能够长时间稳定运行，很少出现因系统本身问题导致的故障，非常适合小型项目的长期使用，不用担心频繁出现数据库方面的状况影响项目进展。

高性能表现

在小型项目常见的读操作以及简单查询场景中，MySQL 的表现十分优异。例如小型的内容管理系统，主要是对已存储的数据进行读取展示，像文章的查询、用户信息的查看等操作，MySQL 都能快速响应，高效地返回结果，保障项目的流畅运行，适用于读多写少的应用场景。

丰富的工具支持

它具备众多功能强大的管理和开发工具，比如 MySQL Workbench。通过这个工具，开发者可以轻松地进行数据库的创建、表结构设计、数据导入导出等操作，还能方便地对数据库进行性能监测和优化，极大地简化了数据库管理和维护工作，就算是经验不太丰富的开发者也能较为顺利地操作。

灵活的存储引擎

MySQL 支持多种存储引擎，像 InnoDB 和 MyISAM 等。InnoDB 是事务型数据库的首选引擎，支持 ACID 事务，还支持行级锁定，从 MySQL 5.5 起成为默认数据库引擎；MyISAM 则拥有较高的插入、查询速度，不过不支持事务。开发者可以根据小型项目的具体需求，选择最契合的存储引擎来优化数据存储和访问方式。

庞大的社区支持

拥有庞大的用户社区以及丰富的在线资源，这意味着当开发者在使用过程中遇到问题时，很容易就能在社区中找到相应的技术支持和解决方案。无论是常见的报错处理，还是寻求性能优化建议、功能扩展思路等，都可以通过社区交流来获取帮助，为项目开发减少了后顾之忧。

综上所述，MySQL 凭借这些优势，在小型网站、内容管理系统以及其他诸多需要可靠性和高性能的小型项目应用中都能发挥出重要作用，是开发者在小型项目数据库选型时的优质选择之一。

小型项目中 MySQL 数据库的基础搭建

安装与配置步骤

在小型项目中使用 MySQL 数据库，首先要完成基础的安装与配置工作，以下为大家详细介绍在常见操作系统下的相关步骤：

Windows 系统下的安装与配置：

确认是否已安装：按【Win+R】打开运行，输入【services.msc】打开服务，在服务列表中查找 MySQL。若有 mysql 服务则表示 Windows 已经安装 MySQL，若没有 MySQL 服务则表示 Windows 没有安装 MySQL。

下载安装包：进入 MySQL 官网（点击进入，选择 Windows 系统，点击下载），选择 “No thanks, just start my download.” 下载安装包。

添加配置文件并安装：

- 将下载的 zip 包解压到想要安装的目录下。

- 进入解压后的目录，在 bin 同等级目录下新建 my.ini，编辑 my.ini，将以下配置粘贴到 my.ini（注意 basedir 是 mysql 的安装路径，datadir 选择数据存放的路径，basedir、datadir 路径使用正斜杠 / 或双斜杠 \，否则起不来服务）：

[mysqld]

# 设置mysql的安装目录

basedir = [你自己mysql的安装路径]

# 设置mysql数据库的数据的存放目录

datadir = [数据存放的路径]

port = 3306

# 允许最大连接数

max_connections = 200

# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统

max_connect_errors = 10

# 服务端使用的字符集默认为UTF8

character-set-server = utf8mb4

# 创建新表时将使用的默认存储引擎

default-storage-engine = INNODB

# 默认使用“mysql_native_password”插件认证

default_authentication_plugin = mysql_native_password

[mysql]

# 设置mysql客户端默认字符集

default-character-set = utf8mb4

[client]

# 设置mysql客户端连接服务端时默认使用的端口

port=3306

default-character-set = utf8mb4

在 mysql 安装路径下的 bin 目录中使用 cmd 打开（输入 cmd 并回车），输入 mysqld --initialize--console 获取生成的初始密码并保存，后期可能会用到，接着执行 mysqld --install mysql 安装 mysql 服务并启动，最后查看 mysql 服务是否已经启动，若没有启动则手动启动 mysql 服务。

登录数据库，修改密码：在 MySQL 安装目录下的 bin 目录下，启动 cmd，进入命令提示符后执行 mysql -u root -p 连接数据库，再通过 ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码'; 修改 MySQL 数据库密码（这里示例设置的用户名为 root，密码可自行设定）。

配置环境变量：

- 进入系统高级设置，进入环境变量设置。

- 配置环境变量，变量值填写 MySQL 的安装路径，同时修改 path 变量进行编辑。

Linux 系统下的安装与配置：

安装前准备：

- 检查是否已经安装过 mysql，执行命令 rpm -qa | grep mysql，若已存在，则执行删除命令（后边为 Mysql 目录） rpm -e --nodeps mysql-xxxx；查询所有 Mysql 对应的文件夹（通过 whereis mysql、find / -name mysql 命令），删除相关目录或文件（使用 rm -rf 命令），验证是否删除完毕同样可通过上述查找命令查看。

- 检查 mysql 用户组和用户是否存在，如果没有，则创建（使用 cat /etc/group | grep mysql、cat /etc/passwd | grep mysql 查看，通过 groupadd mysql、useradd -r -g mysql mysql 创建）。

- 从官网下载适用于 Linux 的 Mysql 安装包（可通过 wget 命令下载，例如 wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz，也可以直接到 mysql 官网选择其他版本进行下载）。

安装 Mysql：

- 在执行 wget 命令的目录下或你的上传目录下找到 Mysql 安装包（如 mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz），执行解压命令 tar xzvf mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz。解压完成后，将该文件移动到 /usr/local/ 下，并将文件夹名称修改为 mysql（若 /usr/local/ 下已经存在 mysql，请将已存在 mysql 文件修改为其他名称，否则后续步骤可能无法正确进行，执行命令如 mv mysql-5.7.24-linux-glibc2.12-x86_64 /usr/local/mysql）。

- 在 /usr/local/mysql 目录下创建 data 目录（执行命令 mkdir /usr/local/mysql/data）。

- 更改 mysql 目录下所有的目录及文件夹所属的用户组和用户，以及权限（执行 chown -R mysql:mysql /usr/local/mysql、chmod -R 755 /usr/local/mysql，若报用户不存在的错误，先执行 groupadd mysql、useradd -r -g mysql mysql 再操作权限更改命令）。

- 编译安装并初始化 mysql，务必记住初始化输出日志末尾的密码（数据库管理员临时密码），命令如下：

cd /usr/local/mysql/bin

./mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql

编辑配置文件 my.cnf（没有就新建），添加配置如下：

[mysqld]

basedir = /usr/local/mysql

datadir = /usr/local/mysql/data

socket = /usr/local/mysql/mysql.sock

character-set-server=utf8

port = 3306

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[client]

socket = /usr/local/mysql/mysql.sock

default-character-set=utf8

启动 mysql 服务器（执行 /usr/local/mysql/support-files/mysql.server start），显示相应结果则说明数据库安装成功。添加软连接，并重启 mysql 服务（执行 ln -s /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql、ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql、service mysql restart）。

开放远程连接，登录 mysql 后执行以下命令：

use mysql;

update user set user.Host='%' where user.User='root';

flush privileges;

执行完上边命令后，通过数据库客户端就可以连上云数据库。还可以设置开机自动启动（包括拷贝服务文件到 init.d 下并重命名为 mysql、赋予可执行权限、添加服务、显示服务列表等操作，具体命令可参考相关步骤）。

macOS 系统下的安装与配置：

系统环境检测：

- 确保系统中不存在 mysql，若存在则需先卸载干净。检测后台是否存在 mysql 进程（通过 ps -ax | grep mysql 命令，如果存在就关闭，输入：kill -9 (./mysqld前面第二个数字)），删除所有 mysql 相关文件（如 sudo rm -rf /usr/local/mysql、sudo rm -rf /usr/local/var/mysql 等一系列相关目录文件）。

- 查看 macOS 版本（在桌面点击左上角的 “苹果” 图标，点击 “关于本机”，点击 “概览”，获取 macos 的系统版本号），查看 macOS 电脑所属的架构（通过 uname -a 指令，主要分为 ARM 和 X86 两种架构，必须要安装对应架构的版本）。

下载适合的 MySQl：前往官方下载网址，查看历史版本，选择适合系统以及适合版本的文件进行下载。

开始安装：下载完成后进行安装操作，打开设置进行查看（不过此时安装仍未完成，需继续后续配置操作）。

设置环境变量：

- 先测试一下，会提示命令没有发现，接着在终端通过 vim 来编辑 .zshrc 配置文件，点击 i 键进入编辑模式，在配置文件中添加如下路径（路径根据实际安装情况确定），然后按 esc 退出编辑模式，输入 :wq！保存退出，再执行 source ~/.zshrc 使配置生效。

- 此时在终端查看 mysql 版本，如果可以看到已经能查到安装的版本了，说明环境已经配好，现在就可以在终端输入 mysql -uroot -p 然后输入密码，进入 MySQL 使用了。

另外，关于设置远程访问权限，缺省状态下，mysql 的用户没有远程访问的权限，以下介绍两种常用方法解决这一问题：

改表法：登录到 mysql（比如在localhost的那台电脑，登入 mysql 后），更改 mysql 数据库里的 user 表里的 host 项，从 "localhost" 改称 "%"。

授权法：在安装 mysql 的机器上运行，示例如下：

mysql -h localhost -u root; --这样应该可以进入MySQL服务器

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION; --赋予任何主机访问数据的权限

FLUSH PRIVILEGES; --修改生效

EXIT; --退出MySQL服务器，这样就可以在其它任何的主机上以root身份登录

数据库与表的创建操作

完成 MySQL 的安装与配置后，接下来就是创建适合小型项目需求的数据库以及数据表了。

例如，要创建一个名为 example_db 的数据库，首先需要登录到 MySQL 服务器（使用命令 mysql -uroot -p 输入密码登录），然后执行以下命令来创建数据库：

CREATE DATABASE IF NOT EXISTS example_db;

这里的 IF NOT EXISTS 子句确保如果这个数据库已经存在，那么命令不会报错而是简单地返回。

数据库创建完成后，选择该数据库并开始创建表，以下是一个创建名为 users 表的示例：

USE example_db;

CREATE TABLE IF NOT EXISTS users (

id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(100) NOT NULL,

email VARCHAR(150) UNIQUE NOT NULL,

password VARCHAR(255) NOT NULL,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

在这个表结构中：

id 是一个自动递增的整数类型字段，作为主键，其能唯一标识表中的每条记录，并且自动为每条新记录生成一个唯一的整数值，方便数据的索引和关联等操作。

name 字段用于存储用户的名字，设置了 NOT NULL 约束，表示该字段不允许为空，以此保证每条用户记录都有对应的名字信息。

email 字段存储用户的邮箱地址，设置了 UNIQUE（唯一性）和 NOT NULL 约束，意味着不能有重复的邮箱地址存在，且不能为空，这有助于保证数据的准确性以及在后续通过邮箱查找用户等操作时的高效性。

password 字段用来存储用户的密码，同样设置为 NOT NULL，保证每条用户记录都有对应的密码信息。

created_at 字段记录用户记录创建的时间，设定了默认值为 CURRENT_TIMESTAMP，也就是在插入新记录时，如果没有给这个字段赋值，它会自动获取当前的时间戳，方便记录数据的创建时间顺序等情况。

再比如，创建一个用于小型内容管理系统中记录文章信息的 articles 表，示例如下：

CREATE DATABASE IF NOT EXISTS cms_db;

USE cms_db;

CREATE TABLE IF NOT EXISTS articles (

article_id INT AUTO_INCREMENT PRIMARY KEY,

title VARCHAR(200) NOT NULL,

content TEXT,

author VARCHAR(100) NOT NULL,

publish_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

category VARCHAR(50),

FOREIGN KEY (category) REFERENCES categories(category_name)

);

这里创建了 cms_db 数据库，并在其中创建 articles 表，除了类似上述主键、非空等设置外，还有 category 字段设置了外键约束，关联到了另一个名为 categories 表中的 category_name 字段，通过这样的外键关联，可以保证数据的一致性和完整性，例如确保文章所属的分类必须是已经在 categories 表中定义好的分类，避免出现无效的分类关联情况。

总之，在创建数据表时，要根据小型项目的具体业务逻辑和数据需求，合理地定义表结构、设置主键保证记录唯一性、通过外键建立表与表之间的关联关系等关键要素，为后续的数据存储、查询、更新等操作做好充分准备。

MySQL 在小型项目中的实战操作技巧

数据的增删改查

在小型项目中，对 MySQL 数据库进行数据的增删改查操作是十分常见且基础的工作，下面为大家介绍如何使用对应的 SQL 语句来实现这些操作，并附上相应示例代码帮助理解。

数据插入（INSERT INTO）：

使用 INSERT INTO 语句可以向已创建好的表中插入新的数据记录。其基本语法格式如下：

INSERT INTO 表名 (列1, 列2,...) VALUES (值1, 值2,...);

例如，我们有一个名为 students 的表，包含 id（自增主键）、name（学生姓名）、age（年龄）和 gender（性别）这几个字段，现在要插入一条学生信息，可以这样写：

INSERT INTO students (name, age, gender) VALUES ('张三', 20, '男');

如果要一次性插入多条数据，语法格式如下：

INSERT INTO 表名 (列1, 列2,...) VALUES (值1, 值2,...), (值3, 值4,...),...;

比如插入多条学生记录：

INSERT INTO students (name, age, gender) VALUES ('李四', 21, '女'), ('王五', 19, '男');

数据更新（UPDATE）：

当需要修改表中已存在的数据时，就会用到 UPDATE 语句，基本语法如下：

UPDATE 表名 SET 列1 = 值1, 列2 = 值2,... WHERE 条件;

这里的 WHERE 条件非常关键，它用于指定要更新哪些符合条件的记录，如果不写 WHERE 条件，那么将会更新表中所有的记录，这通常不是我们期望的结果。例如，要将名为张三的学生年龄修改为 22 岁，代码如下：

UPDATE students SET age = 22 WHERE name = '张三';

数据删除（DELETE FROM）：

使用 DELETE FROM 语句可以删除表中的数据记录，语法格式为：

DELETE FROM 表名 WHERE 条件;

同样，WHERE 条件用于限定要删除的具体记录，若省略它，会删除表中全部数据，需谨慎使用。比如要删除年龄小于 18 岁的学生记录，可以这样写：

DELETE FROM students WHERE age < 18;

数据查询（SELECT）：

SELECT 语句用于从数据库表中检索数据，其基本语法格式如下：

SELECT 列1, 列2,... FROM 表名 WHERE 条件;

例如，要查询所有女学生的信息，可以这样写：

SELECT * FROM students WHERE gender = '女';

如果只想查询部分字段，比如只查询学生的姓名和年龄，代码如下：

SELECT name, age FROM students;

还可以使用一些关键字进行更复杂的查询，像 ORDER BY 用于对查询结果进行排序，例如按照年龄从小到大查询学生信息：

SELECT * FROM students ORDER BY age ASC;

GROUP BY 用于按照指定的字段对结果进行分组，比如统计不同性别的学生人数：

SELECT gender, COUNT(*) FROM students GROUP BY gender;

通过熟练掌握这些 INSERT INTO、UPDATE、DELETE FROM 和 SELECT 语句的使用方法，就能在小型项目中灵活地对 MySQL 数据库中的数据进行相应的增删改查操作了，满足项目不同业务场景下的数据处理需求。

查询优化与索引策略

在小型项目中，随着数据量的逐渐增加以及查询操作的频繁执行，优化查询性能就变得尤为重要，而合理地运用索引策略是提升查询效率的关键所在，下面为大家详细讲解相关内容。

分析查询执行计划：

MySQL 提供了 EXPLAIN 关键字，通过它可以分析查询语句的执行计划，帮助我们了解数据库系统是如何执行该查询操作的。例如，有一个查询员工信息的语句如下：

EXPLAIN SELECT * FROM employees WHERE last_name = "Smith";

执行以上语句后，MySQL 会返回一个结果集，其中包含多个重要的列信息：

id：每个操作的唯一标识符，以 0 开始，在复杂查询（如包含子查询、联合查询等情况）中可以体现执行的先后顺序。

select_type：表示查询的类型，像 SIMPLE（简单查询）、PRIMARY（主查询）等，我们可以根据这个字段了解查询的复杂程度，并且尽量避免过多复杂的子查询、联合查询等，因为它们可能会影响性能，如有可能将子查询转为 JOIN 操作来提高性能。

table：指出操作涉及的表名，若是子查询或派生表，会显示相应的别名。

type：这是非常关键的一个字段，显示了 MySQL 查找表中行的访问类型，常见的有 ALL（全表扫描）、INDEX（使用索引扫描）等，访问类型从好到差排序，我们要尽量避免出现 ALL 这种全表扫描的情况，通常意味着没有使用索引，需要通过创建合适的索引或优化查询条件来避免，尽量让查询使用 const、eq_ref 等高效的访问类型。

possible_keys：显示此查询可能使用的索引，如果该字段为空，说明没有找到可用的索引，往往这就是性能瓶颈的信号，此时就要考虑在相关查询条件涉及的列上创建合适的索引了，尤其是 WHERE、JOIN 或 ORDER BY 语句中涉及的列。

key：代表 MySQL 实际选择使用的索引，若 possible_keys 有值，但 key 字段为空，那就意味着 MySQL 选择不使用索引，这时可以通过 FORCE INDEX 强制使用索引，或者调整索引的设计以及查询条件，使索引更具选择性。

key_len：展示实际使用的索引的字节长度，通过它可以判断索引是否被充分利用，例如对于组合索引，能了解是否使用了所有列，一般来说 key_len 越短，查询效率越高，所以要确保只为必要的列创建索引，避免使用过多的索引列。

rows：表示 MySQL 估计需要扫描的行数，数值越大查询的代价越高，我们可以通过优化查询条件和索引等方式，尽量减少这个值，同时也可以定期使用 ANALYZE TABLE 或 OPTIMIZE TABLE 命令来更新表的统计信息，确保 MySQL 的估算值更准确。

Extra：提供额外的查询相关信息，比如 Using index（查询只使用了索引，无需回表，也就是覆盖索引情况）、Using where（使用了 WHERE 条件过滤）、Using temporary（查询中使用了临时表，常见于复杂的 ORDER BY、GROUP BY 操作中）、Using filesort（使用了文件排序，意味着没有使用索引进行排序，性能较差）等，针对这些情况，我们要尽量避免出现 Using temporary 和 Using filesort，可以通过优化查询或者使用适当的索引来减少临时表的使用以及实现利用索引进行排序操作。

创建合适的索引：

索引的类型及创建方式：

常见的索引类型有普通索引、唯一索引、主键索引、联合索引、全文索引等。

普通索引创建方式如下：

ALTER TABLE `table_name` ADD INDEX index_name (`column`);

唯一索引要求列值必须唯一，允许为 null，创建语句是：

ALTER TABLE `table_name` ADD UNIQUE index_name (`column`);

主键索引是特殊的唯一索引，一张表只能有一个主键，且不允许为 null，创建示例：

ALTER TABLE `table_name` ADD PRIMARY KEY (`column`);

联合索引是同时在多个字段上创建索引，查询效率更高，创建语法为：

ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`, `column3`);

全文索引主要用于匹配字符串文本中的关键字，适用于类似用 like 且以 % 开头无法使用普通索引的情况，创建方式如下：

ALTER TABLE `table_name` ADD FULLTEXT (`column`);

索引设计原则：

- 选择合适的字段创建索引：优先选择在那些频繁用于查询条件（如 WHERE 子句中出现）、排序（ORDER BY 语句中）、分组（GROUP BY 语句中）以及连接（JOIN 条件下）的字段上创建索引，这些场景下索引能有效提升查询速度。同时，区分度低（比如某字段只有很少几种取值情况）、频繁更新的字段以及过长的字段不太适合创建索引，因为区分度低的字段利用索引筛选数据的效果不佳，频繁更新字段会导致同时更新索引表增加负担，过长字段会占用较多磁盘空间。例如对于一张用户表，性别字段区分度相对较低（往往只有男、女两种取值），就不如生日字段适合创建索引；而对于 text 类型这种较长的字段，一般不建议创建索引。

- 联合索引的使用原则：创建联合索引时要遵循最左前缀原则，例如创建了联合索引 key index (a,b,c)，那么可以支持 a、a,b、a,b,c 这几种组合进行查找，但不支持 b,c 这样的查找，也就是查询时要从最左边的字段开始利用索引，所以在设计联合索引时，要将区分度高、经常用于筛选条件的字段放在前面，以此减少查询次数，更快地匹配到所需数据。

- 控制索引数量：索引并非越多越好，每个索引都会占用磁盘空间，而且在修改表（如插入、更新、删除数据时），索引越多，对索引的重构和更新操作就越麻烦，会使更新表变得很耗时，严重影响性能，所以要根据实际业务需求合理控制索引的数量。

索引使用的注意事项：

避免索引失效情况：

- like 语句使用时要注意，以 % 开头的模糊查询会导致索引失效，只有后缀有 %（如 abc% 这种右模糊查询）时索引才有效。

- 在使用 or 语句时，如果 or 前后查询字段只有一个是索引，该索引会失效，只有当 or 左右查询字段均为索引时，才会生效。

- 对于组合索引，如果查询没有使用第一列索引，整个索引都会失效。

- 数据类型出现隐式转化（比如 varchar 类型字段在查询条件中不加单引号，可能会自动转换为 int 型），会使索引无效，产生全表扫描。另外，在索引列上使用 IS NULL 或 IS NOT NULL 操作、not、<>、!= 这些不等于操作符（它们永远不会用到索引，会产生全表扫描）等情况，也会导致索引无法正常使用，对于这些情况可以考虑通过其他方式处理，例如数字类型判断大于 0，字符串类型设置默认值后判断是否等于默认值等。

- 对索引字段进行计算操作或者使用函数时，也可能会使索引失效，比如对有索引的字段进行 LEFT() 等函数操作后，索引可能就无法发挥作用了。

总之，在小型项目中运用 MySQL 时，要充分重视查询优化与索引策略，通过分析执行计划来了解查询情况，创建合适的索引，并注意避免索引失效的各种情形，这样才能提升数据库操作的效率，保障项目的良好性能表现。

MySQL 实战：小型项目中的数据库应用（一）