1. 库操作到底在操作什么刚接触MySQL的时候很多人第一件事就是装环境、配变量、打开命令行敲几句SQL。折腾完mysql -uroot -p能进去了接下来自然就会问一句然后呢然后就是建库、建表、塞数据。这里面的“建库”就是我今天想聊的核心——库的操作。它不是最炫技的部分但却是整个MySQL使用频率最高、也最容易被忽视的地基。你后面所有的建表、索引、存储过程、事务、权限管理全都活在某一个“库”的壳子里。库没建好后面越写越别扭库建错了后面改起来牵一发动全身。这门课我当年学的时候没当回事后来帮人排查问题、接手旧项目、甚至自己搭个人博客的时候才意识到库层面的设计直接决定了后续开发的幸福感。这篇文章不是什么高深理论就是把我实际用MySQL过程里围绕“库”这个维度做过的操作、踩过的坑、总结出的习惯全部摊开来讲一遍。适合谁看刚装好MySQL不知道怎么下手的纯新手上了一阵子课但对字符集、排序规则半懂不懂的初学者还有写过一阵子但从来没认真想过“为什么我的库里中文乱码”“为什么删库那么慢”这类问题的同学。看完这篇文章你至少能把库层面的操作从“会敲命令”提升到“知道自己在敲什么命令”的程度。2. 建库删库查库的基本动作与习惯养成2.1 一条CREATE DATABASE背后的参数含义先看最基础的操作创建一个数据库。CREATE DATABASE mydb;这条语句能跑通但如果你在真实项目里也这么写基本上等于埋雷。为什么因为这里的库名、字符集、排序规则、是否允许重复创建全都没有明确指定MySQL就按照它自己的默认值来。默认值是什么在老版本里默认字符集是latin1存中文就是乱码重灾区在MySQL 8.0里默认变成了utf8mb4情况好一些但万一你的团队标准是utf8mb4_general_ci而默认生成的是utf8mb4_0900_ai_ci后面做数据迁移、比较查询时还是会有一堆隐性问题。所以我实际写库的时候习惯永远带上完整的参数声明CREATE DATABASE IF NOT EXISTS mydb DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;IF NOT EXISTS看着像是废话但在脚本里跑第二遍的时候你就知道它有多重要。没有这个保护脚本重复执行直接报错中断。DEFAULT CHARACTER SET指定了库内所有表默认的字符集只要建表时不另外指定就会继承这层设置。DEFAULT COLLATE是排序规则它决定的是字符串比较的时候按什么规则排是大小写敏感还是不敏感是用更精确的Unicode规则还是更快的传统规则。聊排序规则容易把人绕晕我打个比方。字符集相当于你选了一套字母表比如英文26个字母排序规则就是这套字母表里“谁排在谁前面”的约定。utf8mb4是字母表utf8mb4_general_ci是排序时的约定。ci结尾就是case insensitive大小写不敏感cs结尾就是case sensitive大小写敏感bin结尾就是按二进制比较最严格。实际开发中90%的场景用_general_ci就够了剩下10%看你业务具体要求。2.2 查库与选库SHOW DATABASES和USE的正确姿势库建好了怎么确认它真的存在怎么在多个库之间切换SHOW DATABASES;这条命令会列出当前MySQL实例里所有的库。刚装好的MySQL你至少会看到这几个系统自带的库information_schema、mysql、performance_schema、sys。这些库是MySQL自己用来存元数据、系统信息、性能数据的新手千万不要去里面乱改表结构。我见过有人为了“好奇”去删了mysql库里的user表结果整个实例直接废掉所有用户都连不上最后只能重新初始化数据目录。这种操作不是一句“手滑”能洗清的是真要命的。选库的操作是USEUSE mydb;执行完这一句你后续所有的建表、查询、插入操作就都默认作用在mydb这个库下了。不执行USE也行SQL里可以写全限定名SELECT * FROM mydb.users;但对于日常操作我强烈建议先USE再操作理由有两个第一SQL写起来短不用每个表名前面都带库名前缀第二很多可视化工具比如Navicat、MySQL Workbench连接后默认就会定位到某个库你不USE就可能把表建到默认库里回头找半天。2.3 DROP DATABASE删库之前必须做的三件事删库是库操作里最危险的一个动作。DROP DATABASE mydb;执行完这行命令整个库连同里面所有的表、数据、存储过程、视图直接消失没有回收站。我删库前的固定三连检查第一确认是不是连对了实例。你有没有在测试环境的命令行里敲过生产环境的SQL我有过。那是一个周日晚上我以为自己在本地库执行一条ALTER TABLE结果看错终端窗口把一条DROP语句丢到了同事还在用的共享开发库上。当时要不是有备份大家一周的工作直接清零。所以现在无论多急我删库之前一定先执行一句SEELCT DATABASE();来看当前选的库是谁再执行一句SHOW TABLES;扫一眼里面有什么心里有数了再动手。第二确认有没有备份。哪怕是开发库删之前花十秒钟导出一份也不亏。mysqldump导出整个库mysqldump -uroot -p --databases mydb mydb_backup.sql第三确认有没有外部的连接还在访问这个库。如果有正在运行的定时任务、正在写入数据的服务你把库删了它们的连接不会立刻断但下一笔写入就会报错甚至在某些ORM框架里会引起一连串的异常重试把日志刷爆。可以先执行SHOW PROCESSLIST;看一下当前有哪些线程在跑。删库本身不是什么高技术含量操作难的是删之前你心里有没有数。我见过太多新人一紧张就乱删删完跑来问“有没有办法恢复”结果既没备份也没开binlog神仙也难救。3. 字符集与排序规则库操作里最影响体验的隐藏设置3.1 utf8mb4和utf8的恩怨纠葛聊库操作字符集是绝对绕不开的一座大山。因为数据库是存数据的数据是什么编码决定了你存汉字、存emoji、存各种特殊符号时会不会出问题。MySQL里有个特坑的设置就是utf8。你在MySQL里写CHARACTER SET utf8它其实不是真正的“全量UTF-8”最多只支持3个字节的编码。大部分常用汉字没问题但一旦遇到emoji表情、某些生僻字它就直接罢工要么报错、要么存成一堆乱码。真正的全量UTF-8需要最多4个字节来编码MySQL里对应的是utf8mb4。所以如果你要新建库直接选utf8mb4没有悬念。utf8mb4是utf8的超集能覆盖所有字符不会因为字符集问题导致存不了数据。网上很多老教程还让人用utf8那些文章大多写于很多年前当时utf8mb4还没普及现在再照着做就过时了。我接手过一个小项目原开发在库层面用了latin1表层面又是utf8然后应用连接串里再指定了一遍gbk。三层编码各管各的写出来的中文在数据库里是一堆问号查出来又是一堆乱码。最后排查了整整一下午问题根源就是建库的时候没选对字符集从一开始就埋了雷。3.2 排序规则到底怎么选字符集定了排序规则紧随其后。还是那句话字符集管“能存什么”排序规则管“怎么排序、怎么比较”。拿utf8mb4来说常见的排序规则有这些utf8mb4_general_ci速度优先大小写不敏感兼容性好老项目的默认选择utf8mb4_unicode_ci更符合Unicode规范排序更准确但性能略低一点点utf8mb4_0900_ai_ciMySQL 8.0的新默认值基于Unicode 9.0支持更丰富的语言规则不区分重音如果你做的是中文业务系统排序规则用_general_ci就足够了。做多语言平台、需要精确处理西欧语言重音字符的场景可以用_unicode_ci或_0900_ai_ci。大小写敏感的需求可以单独在建表时对某个字段指定其他排序规则完全没必要在库层面把所有人都锁死。还有一个细节要注意库的字符集和排序规则是“默认值”性质它会传递给新建的表。表已经建好之后再改库的字符集并不会改动已存在表的字符集只影响之后新建的表。这是一个反直觉的坑。很多新手以为执行了一行ALTER DATABASE ... CHARACTER SET xxx全库的表都自动跟着变了实际上完全不是这么回事。所以你如果想调整整个库的字符集得同时处理库、表、字段三个层次最稳妥的方式是导出全部数据、清理重建、再导入或者用ALTER TABLE逐个转换。3.3 建错字符集后的急救方案万一你已经建了库、建了表、存了数据才发现字符集全乱套了怎么办分情况看如果是库和表的定义字符集不对但数据本身还没存乱可以直接改定义ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;注意这里的CONVERT TO它会同时把表定义和表中已有字符串数据都转成新的字符集。如果只是用MODIFY改字段类型那只是改了字段的定义旧数据可能还在用老编码照样乱。实际场景我更建议先导出再导入这样你能在导入之前手动确认数据文件的编码是否正确不会出现“改完之后数据全变问号”的二次灾祸。如果数据已经乱码了那就不是改字符集能解决的得考虑数据恢复。通常做法是把乱码前的原始数据文件找出来确认它最初是什么编码写进去的再通过CONVERT或程序脚本转码找回。这个过程非常痛苦我经历过一次处理了三天才把几万条中文记录救回来。所以还是那句话建库的时候一次性选对utf8mb4后面能省一百个麻烦。4. 修改库定义与批量操作从命令行到可视化的完整链路4.1 ALTER DATABASE能改什么不能改什么建库的时候没想好等上线了发现字符集不对、排序规则不合需求这时候就要用到ALTER DATABASE。ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER DATABASE能改的核心就两件事字符集和排序规则。数据库的名字能不能改MySQL里其实没有直接的RENAME DATABASE命令。网上有些方案是先备份再恢复成新库名或者直接操作系统目录里的文件夹名后一种极不推荐因为MySQL的字典表里记录的数据目录路径不会自动跟着变改了文件夹名会导致整个库打不开。想改库名最安全的方式就是那个常规路子导出、建新库、导入、删旧库。很多人会问那我能不能直接修改默认字符集让所有新建的库都自动变成utf8mb4可以。在MySQL 8.0里默认字符集已经是utf8mb4了如果是老版本可以修改配置文件[mysqld] character-set-serverutf8mb4 collation-serverutf8mb4_general_ci改完重启MySQL服务生效。这一步做完以后CREATE DATABASE不写字符集参数也会默认用utf8mb4。4.2 mysqldump备份与source导入的完整实操先说备份。备份一个库的常规操作mysqldump -uroot -p --single-transaction --set-gtid-purgedOFF --databases mydb mydb_backup.sql--single-transaction这个参数在InnoDB引擎下能让备份过程不锁表业务可以继续写数据--set-gtid-purgedOFF是为了避免在普通恢复环境里带上GTID信息导致导入报错。这两个参数是我日常备份必带的。如果只想备份表结构不要数据mysqldump -uroot -p --no-data --databases mydb mydb_schema.sql导入也简单mysql -uroot -p mydb_backup.sql或者在MySQL命令行里执行SOURCE /path/to/mydb_backup.sql;注意这里SOURCE后面写的是操作系统里的绝对路径不是MySQL里的虚拟路径。导入的时机也有讲究最好在业务低峰期进行因为大批量写入会造成表锁竞争影响线上读写。4.3 Navicat和MySQL Workbench下的库操作体验命令行写多了以后你会越来越依赖可视化工具的效率。Navicat是我用得最多的MySQL客户端它对库的操作非常直观右键一个连接选“新建数据库”弹窗里填库名、选字符集、选排序规则点确定就完事了。备份也方便右键库名选“转储SQL文件”它会自动生成一份完整的建库建表插入数据脚本。MySQL Workbench则是官方出品免费开箱即用。它的“Data Export/Import”功能很强大可以把多个库一次性导出成zip包也可以单独导出某几张表。前端界面侧边栏还能直接查看每个库的详细属性包括表数量、数据大小、字符集等方便快速排查问题。可视化工具不是用来替代命令行的而是辅助你提高效率。真正要精确控制的东西比如在某些特殊场景下拼接批量SQL还是得回到命令行里做。我个人的习惯是日常查询用可视化工具批量变更和危险操作永远走命令行因为命令行能留下操作日志出问题时可追溯。4.4 批量改库名、批量看库体积的实用脚本工作中偶尔会遇到一次性处理几十个库的场景比如把一批测试库统一加上_backup后缀或者把库里所有的表统一查看大小。一个个手敲SQL效率太低这种时候就要学会写一点小脚本。批量给库名加后缀的Shell思路mysql -uroot -p -N -e SHOW DATABASES LIKE test_%; | while read db; do mysqldump -uroot -p --databases $db ${db}_backup.sql mysql -uroot -p -e CREATE DATABASE \${db}_backup\ DEFAULT CHARACTER SET utf8mb4; mysql -uroot -p $db ${db}_backup.sql done这个脚本就是遍历所有test_开头的库逐个备份再恢复到带_backup后缀的新库里。实际操作时建议先在一个测试实例上跑一遍确认逻辑无误再对真实环境执行。查看某个实例下所有库的体积SELECT table_schema AS 库名, ROUND(SUM(data_length index_length) / 1024 / 1024, 2) AS 大小MB FROM information_schema.tables GROUP BY table_schema ORDER BY 大小MB DESC;这条SQL很好用能一眼看出哪些库占空间排查磁盘满的问题时特别管用。5. 权限管理与用户操作库的“门禁”怎么设5.1 只有root行不行很多新手装好MySQL以后从头到尾就一个root用户所有的库、所有权限都在root名下。自己本地开发这么搞没问题但只要库开始被多人访问比如一个开发团队共用一个测试库大家都用root后果就是任何人都有权限删库、改表、清数据。真出了事连是谁干的都不知道。我见过不止一次团队协作事故就是共用root导致的。所以只要你的MySQL不是自己一个人独占我建议立刻开始搞用户权限隔离。5.2 创建用户并授权到具体库创建一个专属用户比如给一个应用建一个只操作某个库的账号CREATE USER app_userlocalhost IDENTIFIED BY StrongPass123!; GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO app_userlocalhost; FLUSH PRIVILEGES;这里的关键点是mydb.*它表示权限只作用于mydb这个库下的所有表其他库一概碰不到。localhost限制了来源IP只能是本机。如果是远程应用要连接把localhost换成%或者更严谨地写成具体IP比如192.168.1.100。MySQL 8.0之后CREATE USER和GRANT语句分得更清楚GRANT不再自动创建用户。所以必须先CREATE USER再GRANT。这也是老教程最容易过时的地方照着老写法直接执行GRANT ... IDENTIFIED BY在8.0里会直接报语法错误。查看用户权限SHOW GRANTS FOR app_userlocalhost;撤销权限REVOKE DELETE ON mydb.* FROM app_userlocalhost;删除用户DROP USER app_userlocalhost;5.3 忘记root密码的急救思路这也是一个高频问题而且越急越容易乱。如果你真的把root密码忘了MySQL 8.0下可以这样处理先停掉MySQL服务然后以跳过授权表的方式启动mysqld_safe --skip-grant-tables 或者修改配置文件my.cnf在[mysqld]下加一行skip-grant-tables重启服务。此时你执行mysql -uroot不需要密码就能进去然后立刻改密码ALTER USER rootlocalhost IDENTIFIED BY NewStrongPass;改完以后把配置文件里那行注释掉或者重启正常模式的服务再测试新密码。需要提醒的是--skip-grant-tables模式等于MySQL把所有权限校验都关了任何人都能无密码登录所以只允许在本地应急使用处理完必须恢复配置并重启。如果这台机器暴露在公网你在skip-grant-tables状态下每多停留一秒都是风险。6. 远程连接、Docker部署与常见故障排查6.1 ERROR 2002连接不上MySQL的经典报错很多刚装完MySQL的人在命令行执行mysql -uroot -p结果看到一行ERROR 2002 (HY000): Cant connect to local MySQL server through socket /var/run/mysqld/mysqld.sock (2)先别慌这句话的意思是MySQL客户端根本连不上MySQL服务双方之间没有建立通信。最常见的原因是MySQL服务根本没启动。Linux下先检查systemctl status mysql如果显示inactive或failed就先启动服务systemctl start mysql还有一种情况是服务启动了但socket文件路径不对。查看MySQL配置文件里socket的配置路径比如[mysqld] socket /var/run/mysqld/mysqld.sock客户端连接时默认会找这个路径如果文件不存在或者路径不一致就会出现上面的报错。可以显式指定socket文件路径连接mysql -uroot -p -S /var/run/mysqld/mysqld.sock这种问题大多是安装配置时留下的环境坑经验不足的人容易卡在这里很久实际上就是把服务和文件路径对一下就好。6.2 Docker部署MySQL时root默认密码怎么拿用Docker跑MySQL现在很常见尤其是做本地开发环境一条命令就能起一个MySQL实例docker run -d \ --name mysql-dev \ -e MYSQL_ROOT_PASSWORDroot123456 \ -p 3306:3306 \ mysql:8.0这种方式的root密码就是你指定的环境变量值。但如果你只设置了MYSQL_ROOT_HOST而没设置MYSQL_ROOT_PASSWORD或者你想用官方镜像自动生成的随机密码可以查日志docker logs mysql-dev 21 | grep GENERATED ROOT PASSWORD日志里会打印一行随机密码用这个密码登录后再自己改掉。Docker里跑MySQL还有一个容易忽略的点容器内的数据目录默认不在宿主机上删容器等于删数据。所以生产级用法一定要挂载数据卷docker run -d \ --name mysql-prod \ -e MYSQL_ROOT_PASSWORDroot123456 \ -p 3306:3306 \ -v /my/own/datadir:/var/lib/mysql \ mysql:8.0后面那个/my/own/datadir是宿主机目录:/var/lib/mysql是容器内MySQL的数劇目录。这样就算容器删了重建数据也还在宿主机上。6.3 Navicat等其他客户端连接不上Navicat连接MySQL提示“Cant connect to MySQL server (10038)”或者“Access denied”通常是两个方向的原因。一个是MySQL服务没开远程访问。MySQL默认只允许localhost连接如果你要用Navicat从另一台机器连得先确认用户host是不是%或者你的IP以及MySQL是否监听了0.0.0.0。查看MySQL启动参数或者配置文件里bind-address这一项[mysqld] bind-address 0.0.0.0改成0.0.0.0表示所有IP都能连进来。这只适合开发环境生产环境建议bind到内网IP不要暴露公网。另一个是防火墙。Linux的firewalld、Windows的防火墙弹窗都可能挡住3306端口。排除思路很简单本机连一下通了说明MySQL没问题再用另一台机器telnet一下端口不通就查防火墙。telnet 192.168.1.10 3306能够建立连接后再考虑用户名密码的问题一步步排查不要一上来就怀疑密码错了。6.4 库打不开或崩溃后的恢复思路有时候你会遇到库打不开比如服务启动时报某个库的表损坏或者直接提示找不到某个表文件。MySQL的InnoDB引擎相对比较皮实但也不是绝对不坏。首先是确认错误日志。MySQL的错误日志通常配置在log_error参数里默认在数据目录下的hostname.err文件。打开日志看具体报错信息别瞎猜。常见的情况是表数据文件损坏可以尝试CHECK TABLE mydb.mytable; REPAIR TABLE mydb.mytable;注意REPAIR TABLE对MyISAM表有效InnoDB表不一定支持。InnoDB的推荐做法是先用备份恢复没有备份的情况下考虑用MySQL自带的工具或专业数据恢复服务。我真的要再强调一次备份的重要性。MySQL没有养成备份习惯就像开电脑不接UPS一样平时看不出区别坏的那一刻就是全部。我个人最低限度是在每个库的表结构或数据有大变更之前先mysqldump一份完整的备份文件。哪怕只是开发库也一样。数据这东西没了是真的没了。7. 绕不开的这些进阶话题与我的操作心得前面聊了库的增删改查、字符集、权限、部署部署排障都是围绕“库”本身的操作。再往下库接出来的就是表、索引、视图、存储过程、触发器这些东西。热搜词里提到了存储过程、索引、行转列、锁表、explain这些虽然不属于库操作的范畴但它们的载体都在库里面。我简单说几个容易踩坑的点。存储过程的典型问题是没有控制好结束分隔符在命令行里写存储过程必须临时把分隔符改成DELIMITER //写完再改回来。不然一遇到分号MySQL就认为语句结束了存储过程体根本没机会执行。索引的问题则相反是太多人乱建索引一个表恨不得建十几个索引结果写入性能被拖垮。explain是分析慢查询SQL的利器用法很简单在一条SELECT前面加EXPLAIN关键字MySQL会告诉你这条SQL走了哪个索引、扫描了多少行排查性能问题第一反应就该是它。行转列在MySQL里可以用CASE WHEN配合聚合函数实现也可以直接用GROUP_CONCAT做成逗号拼接的效果按业务需求选。这些东西你如果系统学过会发现都很简单你没系统学过东搜一段西复制一段也能跑通但出了问题就抓瞎。我的建议是把库操作当成基本功反复练直到CREATE DATABASE、ALTER DATABASE、DROP DATABASE、mysqldump这些命令像呼吸一样自然再去碰进阶功能。我自己的习惯是把常用的库操作SQL整理成一个速查文档每次开新项目、接新环境的时候直接翻出来对照执行。这样既不会漏步骤也能让自己的操作保持统一风格。尤其是CREATE DATABASE这种命令哪怕我已经写了上百次每次还是会带上IF NOT EXISTS和字符集参数这已经成了肌肉记忆。写SQL这件事一致性比花哨重要得多。最稳的代码往往不是你灵光一闪写出来的那种而是你反复验证过、形成固定写法的那种。如果你刚装好MySQL不知道从哪里练手可以先自己建一个库玩玩比如把你的个人通讯录用表结构搭起来先建库、定字符集、设排序规则然后试试备份、恢复、授权一条龙。走完这遍流程你对MySQL的库操作就算是真正入门了接下来再往表、字段、索引方向深入会顺畅得多。
1. 库操作到底在操作什么刚接触MySQL的时候很多人第一件事就是装环境、配变量、打开命令行敲几句SQL。折腾完mysql -uroot -p能进去了接下来自然就会问一句然后呢然后就是建库、建表、塞数据。这里面的“建库”就是我今天想聊的核心——库的操作。它不是最炫技的部分但却是整个MySQL使用频率最高、也最容易被忽视的地基。你后面所有的建表、索引、存储过程、事务、权限管理全都活在某一个“库”的壳子里。库没建好后面越写越别扭库建错了后面改起来牵一发动全身。这门课我当年学的时候没当回事后来帮人排查问题、接手旧项目、甚至自己搭个人博客的时候才意识到库层面的设计直接决定了后续开发的幸福感。这篇文章不是什么高深理论就是把我实际用MySQL过程里围绕“库”这个维度做过的操作、踩过的坑、总结出的习惯全部摊开来讲一遍。适合谁看刚装好MySQL不知道怎么下手的纯新手上了一阵子课但对字符集、排序规则半懂不懂的初学者还有写过一阵子但从来没认真想过“为什么我的库里中文乱码”“为什么删库那么慢”这类问题的同学。看完这篇文章你至少能把库层面的操作从“会敲命令”提升到“知道自己在敲什么命令”的程度。2. 建库删库查库的基本动作与习惯养成2.1 一条CREATE DATABASE背后的参数含义先看最基础的操作创建一个数据库。CREATE DATABASE mydb;这条语句能跑通但如果你在真实项目里也这么写基本上等于埋雷。为什么因为这里的库名、字符集、排序规则、是否允许重复创建全都没有明确指定MySQL就按照它自己的默认值来。默认值是什么在老版本里默认字符集是latin1存中文就是乱码重灾区在MySQL 8.0里默认变成了utf8mb4情况好一些但万一你的团队标准是utf8mb4_general_ci而默认生成的是utf8mb4_0900_ai_ci后面做数据迁移、比较查询时还是会有一堆隐性问题。所以我实际写库的时候习惯永远带上完整的参数声明CREATE DATABASE IF NOT EXISTS mydb DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;IF NOT EXISTS看着像是废话但在脚本里跑第二遍的时候你就知道它有多重要。没有这个保护脚本重复执行直接报错中断。DEFAULT CHARACTER SET指定了库内所有表默认的字符集只要建表时不另外指定就会继承这层设置。DEFAULT COLLATE是排序规则它决定的是字符串比较的时候按什么规则排是大小写敏感还是不敏感是用更精确的Unicode规则还是更快的传统规则。聊排序规则容易把人绕晕我打个比方。字符集相当于你选了一套字母表比如英文26个字母排序规则就是这套字母表里“谁排在谁前面”的约定。utf8mb4是字母表utf8mb4_general_ci是排序时的约定。ci结尾就是case insensitive大小写不敏感cs结尾就是case sensitive大小写敏感bin结尾就是按二进制比较最严格。实际开发中90%的场景用_general_ci就够了剩下10%看你业务具体要求。2.2 查库与选库SHOW DATABASES和USE的正确姿势库建好了怎么确认它真的存在怎么在多个库之间切换SHOW DATABASES;这条命令会列出当前MySQL实例里所有的库。刚装好的MySQL你至少会看到这几个系统自带的库information_schema、mysql、performance_schema、sys。这些库是MySQL自己用来存元数据、系统信息、性能数据的新手千万不要去里面乱改表结构。我见过有人为了“好奇”去删了mysql库里的user表结果整个实例直接废掉所有用户都连不上最后只能重新初始化数据目录。这种操作不是一句“手滑”能洗清的是真要命的。选库的操作是USEUSE mydb;执行完这一句你后续所有的建表、查询、插入操作就都默认作用在mydb这个库下了。不执行USE也行SQL里可以写全限定名SELECT * FROM mydb.users;但对于日常操作我强烈建议先USE再操作理由有两个第一SQL写起来短不用每个表名前面都带库名前缀第二很多可视化工具比如Navicat、MySQL Workbench连接后默认就会定位到某个库你不USE就可能把表建到默认库里回头找半天。2.3 DROP DATABASE删库之前必须做的三件事删库是库操作里最危险的一个动作。DROP DATABASE mydb;执行完这行命令整个库连同里面所有的表、数据、存储过程、视图直接消失没有回收站。我删库前的固定三连检查第一确认是不是连对了实例。你有没有在测试环境的命令行里敲过生产环境的SQL我有过。那是一个周日晚上我以为自己在本地库执行一条ALTER TABLE结果看错终端窗口把一条DROP语句丢到了同事还在用的共享开发库上。当时要不是有备份大家一周的工作直接清零。所以现在无论多急我删库之前一定先执行一句SEELCT DATABASE();来看当前选的库是谁再执行一句SHOW TABLES;扫一眼里面有什么心里有数了再动手。第二确认有没有备份。哪怕是开发库删之前花十秒钟导出一份也不亏。mysqldump导出整个库mysqldump -uroot -p --databases mydb mydb_backup.sql第三确认有没有外部的连接还在访问这个库。如果有正在运行的定时任务、正在写入数据的服务你把库删了它们的连接不会立刻断但下一笔写入就会报错甚至在某些ORM框架里会引起一连串的异常重试把日志刷爆。可以先执行SHOW PROCESSLIST;看一下当前有哪些线程在跑。删库本身不是什么高技术含量操作难的是删之前你心里有没有数。我见过太多新人一紧张就乱删删完跑来问“有没有办法恢复”结果既没备份也没开binlog神仙也难救。3. 字符集与排序规则库操作里最影响体验的隐藏设置3.1 utf8mb4和utf8的恩怨纠葛聊库操作字符集是绝对绕不开的一座大山。因为数据库是存数据的数据是什么编码决定了你存汉字、存emoji、存各种特殊符号时会不会出问题。MySQL里有个特坑的设置就是utf8。你在MySQL里写CHARACTER SET utf8它其实不是真正的“全量UTF-8”最多只支持3个字节的编码。大部分常用汉字没问题但一旦遇到emoji表情、某些生僻字它就直接罢工要么报错、要么存成一堆乱码。真正的全量UTF-8需要最多4个字节来编码MySQL里对应的是utf8mb4。所以如果你要新建库直接选utf8mb4没有悬念。utf8mb4是utf8的超集能覆盖所有字符不会因为字符集问题导致存不了数据。网上很多老教程还让人用utf8那些文章大多写于很多年前当时utf8mb4还没普及现在再照着做就过时了。我接手过一个小项目原开发在库层面用了latin1表层面又是utf8然后应用连接串里再指定了一遍gbk。三层编码各管各的写出来的中文在数据库里是一堆问号查出来又是一堆乱码。最后排查了整整一下午问题根源就是建库的时候没选对字符集从一开始就埋了雷。3.2 排序规则到底怎么选字符集定了排序规则紧随其后。还是那句话字符集管“能存什么”排序规则管“怎么排序、怎么比较”。拿utf8mb4来说常见的排序规则有这些utf8mb4_general_ci速度优先大小写不敏感兼容性好老项目的默认选择utf8mb4_unicode_ci更符合Unicode规范排序更准确但性能略低一点点utf8mb4_0900_ai_ciMySQL 8.0的新默认值基于Unicode 9.0支持更丰富的语言规则不区分重音如果你做的是中文业务系统排序规则用_general_ci就足够了。做多语言平台、需要精确处理西欧语言重音字符的场景可以用_unicode_ci或_0900_ai_ci。大小写敏感的需求可以单独在建表时对某个字段指定其他排序规则完全没必要在库层面把所有人都锁死。还有一个细节要注意库的字符集和排序规则是“默认值”性质它会传递给新建的表。表已经建好之后再改库的字符集并不会改动已存在表的字符集只影响之后新建的表。这是一个反直觉的坑。很多新手以为执行了一行ALTER DATABASE ... CHARACTER SET xxx全库的表都自动跟着变了实际上完全不是这么回事。所以你如果想调整整个库的字符集得同时处理库、表、字段三个层次最稳妥的方式是导出全部数据、清理重建、再导入或者用ALTER TABLE逐个转换。3.3 建错字符集后的急救方案万一你已经建了库、建了表、存了数据才发现字符集全乱套了怎么办分情况看如果是库和表的定义字符集不对但数据本身还没存乱可以直接改定义ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;注意这里的CONVERT TO它会同时把表定义和表中已有字符串数据都转成新的字符集。如果只是用MODIFY改字段类型那只是改了字段的定义旧数据可能还在用老编码照样乱。实际场景我更建议先导出再导入这样你能在导入之前手动确认数据文件的编码是否正确不会出现“改完之后数据全变问号”的二次灾祸。如果数据已经乱码了那就不是改字符集能解决的得考虑数据恢复。通常做法是把乱码前的原始数据文件找出来确认它最初是什么编码写进去的再通过CONVERT或程序脚本转码找回。这个过程非常痛苦我经历过一次处理了三天才把几万条中文记录救回来。所以还是那句话建库的时候一次性选对utf8mb4后面能省一百个麻烦。4. 修改库定义与批量操作从命令行到可视化的完整链路4.1 ALTER DATABASE能改什么不能改什么建库的时候没想好等上线了发现字符集不对、排序规则不合需求这时候就要用到ALTER DATABASE。ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER DATABASE能改的核心就两件事字符集和排序规则。数据库的名字能不能改MySQL里其实没有直接的RENAME DATABASE命令。网上有些方案是先备份再恢复成新库名或者直接操作系统目录里的文件夹名后一种极不推荐因为MySQL的字典表里记录的数据目录路径不会自动跟着变改了文件夹名会导致整个库打不开。想改库名最安全的方式就是那个常规路子导出、建新库、导入、删旧库。很多人会问那我能不能直接修改默认字符集让所有新建的库都自动变成utf8mb4可以。在MySQL 8.0里默认字符集已经是utf8mb4了如果是老版本可以修改配置文件[mysqld] character-set-serverutf8mb4 collation-serverutf8mb4_general_ci改完重启MySQL服务生效。这一步做完以后CREATE DATABASE不写字符集参数也会默认用utf8mb4。4.2 mysqldump备份与source导入的完整实操先说备份。备份一个库的常规操作mysqldump -uroot -p --single-transaction --set-gtid-purgedOFF --databases mydb mydb_backup.sql--single-transaction这个参数在InnoDB引擎下能让备份过程不锁表业务可以继续写数据--set-gtid-purgedOFF是为了避免在普通恢复环境里带上GTID信息导致导入报错。这两个参数是我日常备份必带的。如果只想备份表结构不要数据mysqldump -uroot -p --no-data --databases mydb mydb_schema.sql导入也简单mysql -uroot -p mydb_backup.sql或者在MySQL命令行里执行SOURCE /path/to/mydb_backup.sql;注意这里SOURCE后面写的是操作系统里的绝对路径不是MySQL里的虚拟路径。导入的时机也有讲究最好在业务低峰期进行因为大批量写入会造成表锁竞争影响线上读写。4.3 Navicat和MySQL Workbench下的库操作体验命令行写多了以后你会越来越依赖可视化工具的效率。Navicat是我用得最多的MySQL客户端它对库的操作非常直观右键一个连接选“新建数据库”弹窗里填库名、选字符集、选排序规则点确定就完事了。备份也方便右键库名选“转储SQL文件”它会自动生成一份完整的建库建表插入数据脚本。MySQL Workbench则是官方出品免费开箱即用。它的“Data Export/Import”功能很强大可以把多个库一次性导出成zip包也可以单独导出某几张表。前端界面侧边栏还能直接查看每个库的详细属性包括表数量、数据大小、字符集等方便快速排查问题。可视化工具不是用来替代命令行的而是辅助你提高效率。真正要精确控制的东西比如在某些特殊场景下拼接批量SQL还是得回到命令行里做。我个人的习惯是日常查询用可视化工具批量变更和危险操作永远走命令行因为命令行能留下操作日志出问题时可追溯。4.4 批量改库名、批量看库体积的实用脚本工作中偶尔会遇到一次性处理几十个库的场景比如把一批测试库统一加上_backup后缀或者把库里所有的表统一查看大小。一个个手敲SQL效率太低这种时候就要学会写一点小脚本。批量给库名加后缀的Shell思路mysql -uroot -p -N -e SHOW DATABASES LIKE test_%; | while read db; do mysqldump -uroot -p --databases $db ${db}_backup.sql mysql -uroot -p -e CREATE DATABASE \${db}_backup\ DEFAULT CHARACTER SET utf8mb4; mysql -uroot -p $db ${db}_backup.sql done这个脚本就是遍历所有test_开头的库逐个备份再恢复到带_backup后缀的新库里。实际操作时建议先在一个测试实例上跑一遍确认逻辑无误再对真实环境执行。查看某个实例下所有库的体积SELECT table_schema AS 库名, ROUND(SUM(data_length index_length) / 1024 / 1024, 2) AS 大小MB FROM information_schema.tables GROUP BY table_schema ORDER BY 大小MB DESC;这条SQL很好用能一眼看出哪些库占空间排查磁盘满的问题时特别管用。5. 权限管理与用户操作库的“门禁”怎么设5.1 只有root行不行很多新手装好MySQL以后从头到尾就一个root用户所有的库、所有权限都在root名下。自己本地开发这么搞没问题但只要库开始被多人访问比如一个开发团队共用一个测试库大家都用root后果就是任何人都有权限删库、改表、清数据。真出了事连是谁干的都不知道。我见过不止一次团队协作事故就是共用root导致的。所以只要你的MySQL不是自己一个人独占我建议立刻开始搞用户权限隔离。5.2 创建用户并授权到具体库创建一个专属用户比如给一个应用建一个只操作某个库的账号CREATE USER app_userlocalhost IDENTIFIED BY StrongPass123!; GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO app_userlocalhost; FLUSH PRIVILEGES;这里的关键点是mydb.*它表示权限只作用于mydb这个库下的所有表其他库一概碰不到。localhost限制了来源IP只能是本机。如果是远程应用要连接把localhost换成%或者更严谨地写成具体IP比如192.168.1.100。MySQL 8.0之后CREATE USER和GRANT语句分得更清楚GRANT不再自动创建用户。所以必须先CREATE USER再GRANT。这也是老教程最容易过时的地方照着老写法直接执行GRANT ... IDENTIFIED BY在8.0里会直接报语法错误。查看用户权限SHOW GRANTS FOR app_userlocalhost;撤销权限REVOKE DELETE ON mydb.* FROM app_userlocalhost;删除用户DROP USER app_userlocalhost;5.3 忘记root密码的急救思路这也是一个高频问题而且越急越容易乱。如果你真的把root密码忘了MySQL 8.0下可以这样处理先停掉MySQL服务然后以跳过授权表的方式启动mysqld_safe --skip-grant-tables 或者修改配置文件my.cnf在[mysqld]下加一行skip-grant-tables重启服务。此时你执行mysql -uroot不需要密码就能进去然后立刻改密码ALTER USER rootlocalhost IDENTIFIED BY NewStrongPass;改完以后把配置文件里那行注释掉或者重启正常模式的服务再测试新密码。需要提醒的是--skip-grant-tables模式等于MySQL把所有权限校验都关了任何人都能无密码登录所以只允许在本地应急使用处理完必须恢复配置并重启。如果这台机器暴露在公网你在skip-grant-tables状态下每多停留一秒都是风险。6. 远程连接、Docker部署与常见故障排查6.1 ERROR 2002连接不上MySQL的经典报错很多刚装完MySQL的人在命令行执行mysql -uroot -p结果看到一行ERROR 2002 (HY000): Cant connect to local MySQL server through socket /var/run/mysqld/mysqld.sock (2)先别慌这句话的意思是MySQL客户端根本连不上MySQL服务双方之间没有建立通信。最常见的原因是MySQL服务根本没启动。Linux下先检查systemctl status mysql如果显示inactive或failed就先启动服务systemctl start mysql还有一种情况是服务启动了但socket文件路径不对。查看MySQL配置文件里socket的配置路径比如[mysqld] socket /var/run/mysqld/mysqld.sock客户端连接时默认会找这个路径如果文件不存在或者路径不一致就会出现上面的报错。可以显式指定socket文件路径连接mysql -uroot -p -S /var/run/mysqld/mysqld.sock这种问题大多是安装配置时留下的环境坑经验不足的人容易卡在这里很久实际上就是把服务和文件路径对一下就好。6.2 Docker部署MySQL时root默认密码怎么拿用Docker跑MySQL现在很常见尤其是做本地开发环境一条命令就能起一个MySQL实例docker run -d \ --name mysql-dev \ -e MYSQL_ROOT_PASSWORDroot123456 \ -p 3306:3306 \ mysql:8.0这种方式的root密码就是你指定的环境变量值。但如果你只设置了MYSQL_ROOT_HOST而没设置MYSQL_ROOT_PASSWORD或者你想用官方镜像自动生成的随机密码可以查日志docker logs mysql-dev 21 | grep GENERATED ROOT PASSWORD日志里会打印一行随机密码用这个密码登录后再自己改掉。Docker里跑MySQL还有一个容易忽略的点容器内的数据目录默认不在宿主机上删容器等于删数据。所以生产级用法一定要挂载数据卷docker run -d \ --name mysql-prod \ -e MYSQL_ROOT_PASSWORDroot123456 \ -p 3306:3306 \ -v /my/own/datadir:/var/lib/mysql \ mysql:8.0后面那个/my/own/datadir是宿主机目录:/var/lib/mysql是容器内MySQL的数劇目录。这样就算容器删了重建数据也还在宿主机上。6.3 Navicat等其他客户端连接不上Navicat连接MySQL提示“Cant connect to MySQL server (10038)”或者“Access denied”通常是两个方向的原因。一个是MySQL服务没开远程访问。MySQL默认只允许localhost连接如果你要用Navicat从另一台机器连得先确认用户host是不是%或者你的IP以及MySQL是否监听了0.0.0.0。查看MySQL启动参数或者配置文件里bind-address这一项[mysqld] bind-address 0.0.0.0改成0.0.0.0表示所有IP都能连进来。这只适合开发环境生产环境建议bind到内网IP不要暴露公网。另一个是防火墙。Linux的firewalld、Windows的防火墙弹窗都可能挡住3306端口。排除思路很简单本机连一下通了说明MySQL没问题再用另一台机器telnet一下端口不通就查防火墙。telnet 192.168.1.10 3306能够建立连接后再考虑用户名密码的问题一步步排查不要一上来就怀疑密码错了。6.4 库打不开或崩溃后的恢复思路有时候你会遇到库打不开比如服务启动时报某个库的表损坏或者直接提示找不到某个表文件。MySQL的InnoDB引擎相对比较皮实但也不是绝对不坏。首先是确认错误日志。MySQL的错误日志通常配置在log_error参数里默认在数据目录下的hostname.err文件。打开日志看具体报错信息别瞎猜。常见的情况是表数据文件损坏可以尝试CHECK TABLE mydb.mytable; REPAIR TABLE mydb.mytable;注意REPAIR TABLE对MyISAM表有效InnoDB表不一定支持。InnoDB的推荐做法是先用备份恢复没有备份的情况下考虑用MySQL自带的工具或专业数据恢复服务。我真的要再强调一次备份的重要性。MySQL没有养成备份习惯就像开电脑不接UPS一样平时看不出区别坏的那一刻就是全部。我个人最低限度是在每个库的表结构或数据有大变更之前先mysqldump一份完整的备份文件。哪怕只是开发库也一样。数据这东西没了是真的没了。7. 绕不开的这些进阶话题与我的操作心得前面聊了库的增删改查、字符集、权限、部署部署排障都是围绕“库”本身的操作。再往下库接出来的就是表、索引、视图、存储过程、触发器这些东西。热搜词里提到了存储过程、索引、行转列、锁表、explain这些虽然不属于库操作的范畴但它们的载体都在库里面。我简单说几个容易踩坑的点。存储过程的典型问题是没有控制好结束分隔符在命令行里写存储过程必须临时把分隔符改成DELIMITER //写完再改回来。不然一遇到分号MySQL就认为语句结束了存储过程体根本没机会执行。索引的问题则相反是太多人乱建索引一个表恨不得建十几个索引结果写入性能被拖垮。explain是分析慢查询SQL的利器用法很简单在一条SELECT前面加EXPLAIN关键字MySQL会告诉你这条SQL走了哪个索引、扫描了多少行排查性能问题第一反应就该是它。行转列在MySQL里可以用CASE WHEN配合聚合函数实现也可以直接用GROUP_CONCAT做成逗号拼接的效果按业务需求选。这些东西你如果系统学过会发现都很简单你没系统学过东搜一段西复制一段也能跑通但出了问题就抓瞎。我的建议是把库操作当成基本功反复练直到CREATE DATABASE、ALTER DATABASE、DROP DATABASE、mysqldump这些命令像呼吸一样自然再去碰进阶功能。我自己的习惯是把常用的库操作SQL整理成一个速查文档每次开新项目、接新环境的时候直接翻出来对照执行。这样既不会漏步骤也能让自己的操作保持统一风格。尤其是CREATE DATABASE这种命令哪怕我已经写了上百次每次还是会带上IF NOT EXISTS和字符集参数这已经成了肌肉记忆。写SQL这件事一致性比花哨重要得多。最稳的代码往往不是你灵光一闪写出来的那种而是你反复验证过、形成固定写法的那种。如果你刚装好MySQL不知道从哪里练手可以先自己建一个库玩玩比如把你的个人通讯录用表结构搭起来先建库、定字符集、设排序规则然后试试备份、恢复、授权一条龙。走完这遍流程你对MySQL的库操作就算是真正入门了接下来再往表、字段、索引方向深入会顺畅得多。
