4. 关系型数据库入门：MySQL/PostgreSQL安装、数据库与表创建、SQL基础查询语句

各位同学，咱们今天聊点实在的。SCADA系统里，历史数据往哪存？说白了，就是关系型数据库。我做了这么多年项目，见过用MySQL的，也见过用PostgreSQL的。说实话，没有绝对的好坏，关键看你的场景。

我个人习惯，中小型SCADA项目用MySQL，上手快，社区活跃。大型项目或者对数据完整性要求极高的，我倾向PostgreSQL。嗯，这里要注意，选型不是拍脑袋，得看你的数据量、并发数、还有团队的技术栈。

4.1 安装MySQL与PostgreSQL

先说说安装。很多新手一上来就纠结版本，其实没必要。我建议直接用最新的稳定版，省心。

4.1.1 MySQL安装

Windows下，去官网下载MySQL Installer，一路Next就行。Linux下更简单：

# Ubuntu/Debian
sudo apt update
sudo apt install mysql-server

# CentOS/RHEL
sudo yum install mysql-server
sudo systemctl start mysqld

安装完后，记得跑一下安全脚本：

sudo mysql_secure_installation

这个脚本会帮你设置root密码、删除匿名用户、禁止远程root登录。我在项目中遇到过，有人图省事跳过了这步，结果数据库被黑了。嗯，血的教训。

4.1.2 PostgreSQL安装

PostgreSQL的安装也类似：

# Ubuntu/Debian
sudo apt install postgresql postgresql-contrib

# CentOS/RHEL
sudo yum install postgresql-server postgresql-contrib
sudo postgresql-setup initdb
sudo systemctl start postgresql

安装完成后，默认会创建一个叫postgres的系统用户。你需要切换到该用户才能操作数据库：

sudo -i -u postgres
psql
我的小建议： 刚接触PostgreSQL时，可能会觉得它的用户管理比MySQL复杂。别怕，多用几次就习惯了。我曾经在项目里因为搞混了pg_hba.conf的配置，折腾了一下午。你想想看，配置文件里那几行认证规则，写错了就登不进去。

4.2 创建数据库与表

数据库装好了，接下来就是建库建表。这是基本功，但也是容易出坑的地方。

4.2.1 创建数据库

MySQL里这样创建：

CREATE DATABASE scada_history
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

PostgreSQL里这样：

CREATE DATABASE scada_history
  ENCODING 'UTF8'
  LC_COLLATE 'zh_CN.UTF-8'
  LC_CTYPE 'zh_CN.UTF-8';

为什么要指定字符集？说白了，SCADA系统里可能存中文标签、中文报警信息。不设置好字符集，存进去的数据查出来是乱码，那就尴尬了。

4.2.2 创建表

咱们以SCADA历史数据表为例。这张表要存什么？时间戳、点位ID、数值、质量戳。我一般这样设计：

-- MySQL版本
CREATE TABLE analog_history (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  point_id INT NOT NULL,
  timestamp DATETIME NOT NULL,
  value DOUBLE NOT NULL,
  quality TINYINT DEFAULT 0,
  INDEX idx_point_time (point_id, timestamp)
) ENGINE=InnoDB;

-- PostgreSQL版本
CREATE TABLE analog_history (
  id BIGSERIAL PRIMARY KEY,
  point_id INTEGER NOT NULL,
  timestamp TIMESTAMP NOT NULL,
  value DOUBLE PRECISION NOT NULL,
  quality SMALLINT DEFAULT 0
);

CREATE INDEX idx_point_time ON analog_history (point_id, timestamp);

关键点： 注意看索引。我见过太多人建表时不加索引，结果数据量一上来，查询慢得像蜗牛。你想想看，SCADA系统一天可能产生几百万条记录，没有索引，全表扫描，那画面太美我不敢看。

4.3 SQL基础查询语句

SQL查询，说白了就是跟数据库对话。你问它要什么，它给你什么。但问法不同，效率天差地别。

4.3.1 基本查询

最简单的，查所有数据：

SELECT * FROM analog_history;

但实际项目中，我几乎不用SELECT *。为什么？因为SCADA系统的表字段可能很多，全查出来浪费带宽和内存。我习惯只查需要的字段：

SELECT point_id, timestamp, value
FROM analog_history
WHERE point_id = 101
  AND timestamp >= '2024-01-01 00:00:00'
  AND timestamp < '2024-01-02 00:00:00';

4.3.2 聚合查询

SCADA系统里，经常要算平均值、最大值、最小值。比如查某个点位一天内的数据统计：

SELECT
  point_id,
  AVG(value) AS avg_value,
  MAX(value) AS max_value,
  MIN(value) AS min_value,
  COUNT(*) AS record_count
FROM analog_history
WHERE point_id = 101
  AND timestamp >= '2024-01-01 00:00:00'
  AND timestamp < '2024-01-02 00:00:00'
GROUP BY point_id;

注意： 聚合查询时，GROUP BY后面的字段必须和SELECT中的非聚合字段一致。我曾经在项目里写漏了，结果查出来的数据乱七八糟，排查了半天才发现是SQL写错了。

4.3.3 时间范围查询

SCADA历史数据回放，核心就是按时间范围查数据。这里有个性能优化的点：

-- 高效写法：利用索引
SELECT timestamp, value
FROM analog_history
WHERE point_id = 101
  AND timestamp BETWEEN '2024-01-01 08:00:00' AND '2024-01-01 09:00:00'
ORDER BY timestamp;

-- 低效写法：函数包裹索引字段
SELECT timestamp, value
FROM analog_history
WHERE point_id = 101
  AND DATE(timestamp) = '2024-01-01'
ORDER BY timestamp;

看出区别了吗？第二种写法用了DATE()函数，索引就失效了。数据量小的时候没感觉，几百万条数据时，性能差距可能是几十倍。

4.3.4 分页查询

回放历史数据时，不可能一次查全部。分页是必须的：

-- MySQL
SELECT timestamp, value
FROM analog_history
WHERE point_id = 101
ORDER BY timestamp
LIMIT 100 OFFSET 0;

-- PostgreSQL
SELECT timestamp, value
FROM analog_history
WHERE point_id = 101
ORDER BY timestamp
LIMIT 100 OFFSET 0;

嗯，这里语法是一样的。但要注意，OFFSET越大，查询越慢。我建议用游标或者基于上次查询的最后一条记录来分页，而不是用OFFSET。

4.4 实战经验总结

说了这么多，最后分享几点我在SCADA项目中的实际经验：

  • 表设计要预留字段： 我习惯在每个表里加一个remark字段，万一以后要扩展呢？
  • 时间字段用UTC： 存储时统一用UTC，展示时再转本地时间。否则不同时区的数据混在一起，你会疯掉的。
  • 定期清理历史数据： SCADA数据增长很快，我一般会写个定时任务，删除90天前的原始数据，只保留聚合后的统计数据。
  • 备份！备份！备份！ 重要的事情说三遍。我曾经因为硬盘坏了，丢了一周的历史数据，从那以后，每天自动备份成了我的铁律。

避坑指南： 我曾经在项目里用DELETE删除大量历史数据，结果事务日志暴涨，把磁盘撑爆了。后来改用TRUNCATE或者分批删除，再也没出过问题。你想想看，几百万条数据的DELETE，那可不是闹着玩的。

好了，这一章的内容就到这里。关系型数据库是SCADA系统的基石，把基础打牢了，后面的数据归档和回放功能才能做得稳。下一章，咱们聊聊如何用Python操作这些数据库，实现数据的读写。

← 上一章 📖 返回目录 下一章 →