Python数据库学习心得：SQLite、MySQL、PostgreSQL优缺点-北京尧图网络

前言先说一个方法论问题「优缺点」这个说法脱离场景是没有意义的。SQLite 的「不支持高并发写」在桌面笔记应用里根本不是缺点因为那里就不存在并发写PostgreSQL 的「功能丰富」在一个只存几十行配置表的小工具里也换不来任何收益。所以本文不做「谁更好」的排名只讲三者在架构上有什么本质差别以及这些差别在什么场景下会变成你的问题。写这类对比时最常见的三个错误说法先纠正掉「SQLite 是小号的 MySQL」。不对。它们不是「同一类东西的不同规模」而是两种架构SQLite 是嵌入式库你的进程直接读写数据库文件MySQL / PostgreSQL 是客户端-服务器系统你的进程通过协议和一个独立的服务端进程通信。这个差别决定了并发模型、部署方式和运维成本的完全不同。「MySQL 默认存储引擎是 MyISAM」。这是十年前的说法。MySQL 5.5 起默认存储引擎就是 InnoDBMySQL 8.4 的官方文档里仍然明确写着「InnoDBMySQL 8.4 的默认存储引擎」。「PostgreSQL 比 MySQL 慢」。这类笼统结论没有意义性能取决于具体负载、索引设计、硬件和版本。不要相信没有测试条件的对比结论。下面从架构、并发、SQL 能力、运维、Python 接入五个维度拆开讲。一、架构定位这是所有差异的源头维度SQLiteMySQLPostgreSQL形态嵌入式库进程内客户端-服务器客户端-服务器数据存放一个文件或内存服务端管理的目录服务端管理的目录部署零部署import即用需安装并启动服务端需安装并启动服务端网络访问无有有账号与权限文件系统权限无内置用户体系有完整的用户/授权体系有完整的用户/授权体系多用户同一台机器、同一文件系统网络内多机多用户网络内多机多用户典型场景桌面应用、移动端、测试、单机工具Web 应用、中小型业务系统复杂业务、分析型负载、地理信息等这张表的第一行解释了其余所有行。嵌入式意味着没有网络往返、没有连接管理、没有服务端进程要运维代价是没有网络访问、没有内置的账号体系、并发写入受限于文件锁。客户端-服务器意味着反过来的一切。二、并发模型最容易被低估的差异SQLite 的写入是全局串行的。同一时刻只允许一个写事务写操作期间数据库文件被锁住。开启 WAL预写日志模式后读操作可以和写操作并发这是 WAL 的主要价值但它不会让多个写者并行。所以适合单进程应用、读多写少、每台设备一个本地库。不适合多个应用服务器同时高频写同一个库文件比如把 SQLite 文件放在网络共享盘上给多台机器写——这是典型的踩坑方式。MySQL 和 PostgreSQL 走的是另一条路多版本并发控制MVCC读写互不阻塞写与写之间靠行级锁InnoDB或更细粒度的锁竞争。既然服务端在处理并发你的应用就可以横向扩展。但要注意数据库层的并发只是第一层。Python 侧的 GIL全局解释器锁意味着同一个进程内同一时刻只有一个线程在执行字节码所以多线程对 I/O 密集的数据库操作有效等待网络时释放 GIL对 CPU 密集的处理无效。想用多核跑 CPU 密集任务得用多进程。这两件事经常被混在一起谈其实是两个独立层次的问题。Python 标准库的sqlite3模块还多一层约束连接对象默认不允许跨线程使用check_same_threadTrue。真要跨线程就每个线程各建一个连接。三、SQL 能力与数据类型能力SQLiteMySQLPostgreSQL类型系统动态类型类型亲和性列声明是建议不是强制静态类型有严格的列类型静态类型类型最丰富原生 JSONjson函数族JSON/JSONB类型8.0 起json/jsonb类型索引与运算符完善数组类型无原生数组无原生数组原生数组类型窗口函数3.25 起支持8.0 起支持长期支持CTE公用表表达式支持8.0 起支持长期支持修改数据后直接返回行支持RETURNINGSQLite 3.35 起不支持RETURNING支持RETURNING全文检索FTS5 扩展内置全文索引tsvector/tsquery长期支持改表结构能力有限改名、加列、删列3.35 起等复杂改动要重建表支持较完整的ALTER TABLE支持较完整的ALTER TABLE外键约束支持但默认关闭需每个连接开启支持支持几点展开SQLite 的动态类型是个双刃剑。它的列上写的是「类型亲和性」type affinity不是强制约束——INTEGER列里理论上可以塞字符串。这让建表很省事也意味着数据质量要靠应用层或CHECK约束去保证。PostgreSQL 的RETURNING很实用INSERT ... RETURNING id能一次拿到新生成的主键省掉一次回查。MySQL 没有这个能力只能靠自增主键的LAST_INSERT_ID()Python 驱动里通常表现为cursor.lastrowid而且批量插入后这个值不可靠。jsonb是 PostgreSQL 的强项可以建索引、可以用运算符查询内部字段。MySQL 8.0 也有JSON类型功能在持续补齐。SQLite 的外键默认关闭且是每连接开关。这一条在「从 SQLite 迁到 PostgreSQL」时最容易出问题——在 SQLite 上跑得好好的删除逻辑到了强制外键的 PostgreSQL 上直接报错。四、Python 侧怎么接数据库驱动说明SQLitesqlite3标准库import即用无需安装随 Python 分发MySQLPyMySQL纯 Python、mysql-connector-python官方、mysqlclientC 扩展都要pip install参数风格多为%sPostgreSQLpsycopg3.x 系列、psycopg2都要pip install参数风格为%s三者的驱动都实现DB-API 2.0PEP 249所以接口形态基本一致connect()→cursor()→execute(sql, params)→fetch*()→commit()/rollback()→close()。差异在细节差异点表现占位符风格SQLite 用?或:name不支持数字占位符MySQL / PostgreSQL 驱动用%s数据库参数名MySQL 驱动常用databasePostgreSQL 风格驱动常用dbname自动提交SQLite 与各 MySQL 驱动默认不自动提交PostgreSQL 驱动默认不自动提交事务控制SQLite 有with con:但不关连接MySQL 驱动也有类似语义因此「换库」最麻烦的从来不是换驱动而是 SQL 方言和事务语义。参数占位符从?换成%s是一行改动但INSERT OR REPLACE、AUTOINCREMENT、LIMIT ? OFFSET ?这些写法各有各的方言迁移时要逐个核对。五、同一件事三种写法下面这段在三种库上都能跑SQLite 部分可直接复制执行因为它不需要任何外部依赖。观察点是流程完全一样只有连接参数和占位符风格在变。# 适用于 Python 3.8这一段用标准库 sqlite3无需安装任何东西import sqlite3conn sqlite3.connect(:memory:)conn.row_factory sqlite3.Rowtry:with conn.cursor() as cur:cur.execute(CREATE TABLE t(id INTEGER PRIMARY KEY, name TEXT))cur.execute(INSERT INTO t(name) VALUES (?), (示例,))cur.execute(SELECT id, name FROM t WHERE name ?, (示例,))row cur.fetchone()print(row[id], row[name])conn.commit()finally:conn.close()预期输出1 示例把这段代码换到另外两个库上需要改的只有这几处需要改的地方SQLiteMySQLPyMySQLPostgreSQLpsycopg 2导入import sqlite3import pymysqlimport psycopg2建连接connect(:memory:)connect(host, user, password, database, charsetutf8mb4)connect(host, user, password, dbname)占位符?%s%s行工厂conn.row_factory sqlite3.Rowcursorclasspymysql.cursors.DictCursorcursor_factorypsycopg2.extras.RealDictCursor自增主键INTEGER PRIMARY KEYINT AUTO_INCREMENTSERIAL或GENERATED AS IDENTITY上下文管理器是否关连接否否否psycopg 3 会关这张表就是「DB-API 统一了什么、没统一什么」的实证统一的是流程和异常层次没统一的是连接参数名、占位符、行工厂钩子和 DDL 方言。六、按场景选型场景建议理由桌面/单机小工具、配置文件式存储SQLite零部署一个文件带走移动端 App 本地存储SQLite无服务端省电省资源单元测试、CI 里的临时库SQLite:memory:快、干净、无需外部依赖中小型 Web 应用读多写少MySQL 或 PostgreSQL有服务端支持多用户与并发复杂查询、JSON/数组/地理数据PostgreSQL类型和索引能力更强团队已有 MySQL 生态与运维经验MySQL迁移成本往往比理论优势更重要多台服务器同时高频写同一个库必须用客户端-服务器方案SQLite 的写是全局串行的一条经验「先用 SQLite等它真的成为瓶颈再换」通常比「一开始就上服务端」更划算前提是你把数据访问层封好切换时不至于满地改 SQL。反之如果一开始就知道会出现多机并发写、需要账号权限体系、需要网络访问那就直接上 MySQL 或 PostgreSQL。常见坑点1. 把 SQLite 当「小号 MySQL」用❌ 把 SQLite 库文件放在网络共享盘上让多台应用服务器同时写。✅ SQLite 的写是全局串行的且依赖本地文件锁需要多机并发写就换成客户端-服务器方案。2. 用「MySQL 默认是 MyISAM」的知识写新代码❌ 建表时不指定引擎又基于「MyISAM 不支持事务」的印象去写代码——实际默认是 InnoDB事务是生效的。✅ 现代 MySQL5.5 起默认 InnoDB支持事务、行级锁和崩溃恢复。要显式指定就写ENGINEInnoDB。3. 迁移到 PostgreSQL 后外键开始报错❌ 在 SQLite 上从来没开过PRAGMA foreign_keysON删数据很随意迁到 PostgreSQL 后被强制外键约束拦下。✅ 迁移前先做一次数据一致性核查把 SQLite 侧的孤儿数据清掉在 SQLite 上也每个连接都开启外键让行为提前对齐。4. 指望用RETURNING拿 MySQL 的新主键❌cur.execute(INSERT ... RETURNING id)在 MySQL 上直接是语法错误。✅ MySQL 用自增主键 cur.lastrowid批量插入后用lastrowid不可靠需要新主键就逐条插入或用业务唯一键回查。5. 以为换了驱动就完成了「换库」❌ 只把import pymysql改成import psycopgSQL 里?占位符、INSERT OR REPLACE、AUTOINCREMENT通通没改。✅ 迁移清单要包含占位符风格、自增列写法、冲突处理语法、分页语法、日期函数、字符串函数。换驱动只是第一行。6. 把「多线程」当成「解决数据库并发」的万能药❌ 用threading开 20 个线程去跑 CPU 密集的数据处理期望加速——受 GIL 限制同一进程内同一时刻只有一个线程执行字节码。✅ CPU 密集用multiprocessingI/O 密集等数据库响应多线程才有效。数据库服务端的并发是另一层问题两件事分开看。7. 从 SQLite 复制一个库文件当备份❌ 直接copy一个正在被写入的.db尤其是开了 WAL 之后可能拿到不一致的快照。✅ 用sqlite3的Connection.backup()做在线备份或先断开所有连接再整体复制。8. 相信没有测试条件的性能对比结论❌ 照着某篇「某某数据库快 3 倍」的文章做选型。✅ 用你自己真实的数据量、查询模式和硬件做基准测试只拿可复现的测试结果做决策。总结维度SQLiteMySQLPostgreSQL架构嵌入式客户端-服务器客户端-服务器并发单写者WAL 下读写可并发MVCC 行级锁MVCC锁粒度更细类型系统动态类型亲和性静态静态类型丰富数组、JSONB、范围运维成本几乎为零中等中等偏高可调项更多最适合单机、桌面、测试Web 应用、成熟生态复杂查询、分析、扩展需求选型的心态应该是先按架构差别排除不可能的选项再在剩下的选项里比细节。SQLite 和另外两个不是一个量级上的同类产品把它们并列比较本身就是一种误导而 MySQL 与 PostgreSQL 的取舍多半取决于团队手里的运维经验和既有生态而不是纸面功能表上的几行差异。

Python数据库学习心得：SQLite、MySQL、PostgreSQL优缺点

相关推荐

深度学习糖尿病足溃疡风险评分系统：数据到部署全流程

TurboQuant存储格式详解：2/4个值如何挤进一个字节完成比特打包

DeepSeek-R1推理模型提示语设计实战指南

更多资讯

自制无人机视角小目标检测数据集：VOC/YOLO/COCO格式转换与增强实战

DataX db2writer 插件：DB2 批量写入与数据迁移实战

Linux下NVMe SSD故障诊断与数据抢救实战指南

LLM Internals 交叉熵损失函数详解：GPT 训练背后的核心数学，为什么取负对数？

让Claude用给5岁小孩讲故事的方式解释任何技术概念：ELI5技能完整入门指南

恶劣天气三维重建：3D高斯Splatting雨雾场景全流程解析

今日更新

交通拥堵预测毕设：基于Python+LSTM的车辆流量预测系统全流程解析

MATLAB/Simulink搭建10机39节点电力系统暂态稳定仿真实战

无人机航拍三维重建全流程：从SfM到网格生成的避坑指南

本周精选

交通拥堵预测毕设：基于Python+LSTM的车辆流量预测系统全流程解析

MATLAB/Simulink搭建10机39节点电力系统暂态稳定仿真实战

无人机航拍三维重建全流程：从SfM到网格生成的避坑指南

本月热门

我发现了一个新思路：用 Remotion + Claude Code 像写代码一样自动化生成短视频

Windows下 Codex 中 Chrome 和 Computer Use 插件不可用问题排查及解决参考方式：TaoToken 统一 Key 配置与验证

2026 大模型集体涨价：用 Python 做企业 Token 成本测算与选型避坑（附配置）

读完文章，想聊聊您的网站？