文章目录MySQL入门保姆级教程库操作从入门到不删库跑路一、创建仓库create database 语法全解1.1 最简版一键建仓1.2 进阶版指定仓库的“语言配置”二、深挖字符集与校验规则别等乱码才后悔2.1 字符集管「能存什么文字」2.2 校验规则管「怎么对比和排序」场景1不区分大小写utf8_general_ci场景2区分大小写utf8_bin2.3 两者的关系2.4 实战选型建议2.5 常用查看命令2.6 底层视角数据库在磁盘上长啥样三、仓库日常运维查看、修改、删除3.1 查看所有数据库3.2 查看数据库的“出生证明”3.3 修改数据库配置3.4 删除数据库高危操作四、备份与恢复数据安全的最后一道防线4.1 备份整个数据库4.2 恢复数据库4.3 进阶备份玩法五、连接监控看看谁在“逛”你的仓库六、新手最佳实践总结MySQL入门保姆级教程库操作从入门到不删库跑路注本文所有 SQL 语句统一采用小写书写。MySQL 语法本身对关键字大小写不敏感大写小写执行效果完全一致小写写法更贴近日常敲代码的手写习惯读起来也更清爽个人练习用完全没问题。各位初学MySQL的小伙伴咱们今天聊最基础的「库操作」。你可以把MySQL想象成一个超大的物流园区每个数据库就是园区里的一个独立仓库——你要先有仓库才能往里面放货架数据表、堆货物数据。今天就手把手教你怎么建仓库、改仓库、备份仓库以及怎么避免“删库跑路”的经典悲剧。一、创建仓库create database 语法全解1.1 最简版一键建仓最基础的创建语句非常简单一行搞定createdatabasedb1;执行完你就拥有了一个叫db1的仓库。但这里有个坑如果这个名字的仓库已经存在再执行这句就会直接报错中断。所以稳妥起见建议加上「防重复保险」if not existscreatedatabaseifnotexistsdb1;它的意思很直白有就算了没有再建绝不报错。 新手高频报错提示遇到ERROR 1064 (42000)语法错误先看报错信息里near xxx指向的位置90%都是单词拼写错误比如把database少写一个 t 拼成dabase、少逗号、少分号这类低级失误。1.2 进阶版指定仓库的“语言配置”建仓库的时候我们可以指定两个核心配置字符集character set决定仓库能存什么语言的“货物”比如中文、英文、表情符号校验规则collate决定仓库盘点、查找货物时的“比对规则”比如区分不区分大小写 新手必踩坑预警很多教程里写的utf8在MySQL里并不是真正的UTF-8它实际叫utf8mb3最多只支持3字节字符存不了emoji、生僻汉字。业界标准做法是使用utf8mb4这才是完整的4字节UTF-8实现。标准的建库语句推荐这么写-- 创建使用utf8mb4字符集的数据库db2createdatabasedb2defaultcharactersetutf8mb4;-- 创建使用utf8mb4字符集 通用不区分大小写校验的数据库db3createdatabasedb3defaultcharactersetutf8mb4defaultcollateutf8mb4_general_ci;小知识点charsetutf8mb4和character set utf8mb4写法完全等价前者是简写后者是完整关键词效果一模一样。二、深挖字符集与校验规则别等乱码才后悔很多新手会把这俩概念混在一起其实它们分工完全不同一个管“存”一个管“比”。2.1 字符集管「能存什么文字」计算机底层只认识0和1字符集就是一张翻译对照表存数据的时候把文字转成二进制读数据的时候把二进制转回文字。字符集选错最直接的后果就是乱码latin1只能存英文、西欧字母存中文直接变成问号???utf8MySQL特有阉割版3字节UTF-8支持常用中文但存不了emoji表情、生僻汉字utf8mb4完整的4字节UTF-8中文、emoji、生僻字全能存新项目统一用这个一句话总结字符集决定了数据库里能存哪些文字。2.2 校验规则管「怎么对比和排序」校验规则必须依附于字符集存在一个字符集可以配套很多套校验规则。它管的是文字之间怎么判断相等、怎么排序。最常见的就两类效果天差地别后缀_ciCase Insensitive不区分大小写后缀_binBinary二进制精确比对区分大小写咱们用实际例子直观感受一下区别场景1不区分大小写utf8_general_cicreatedatabasetest1collateutf8_general_ci;usetest1;createtableperson(namevarchar(20));insertintopersonvalues(a),(A),(b),(B);查询nameaselect*frompersonwherenamea;结果a和A都出来了系统认为大小写是同一个值。排序效果select*frompersonorderbyname;结果A, a, B, b大小写字母排在一起。场景2区分大小写utf8_bincreatedatabasetest2collateutf8_bin;usetest2;createtableperson(namevarchar(20));insertintopersonvalues(a),(A),(b),(B);同样查询nameaselect*frompersonwherenamea;结果只返回a大小写严格区分完全精确匹配。排序效果select*frompersonorderbyname;结果A, B, a, b所有大写字母按ASCII码排在小写前面。2.3 两者的关系打个比方字符集 一本字典规定了里面有哪些字校验规则 这本字典的排序规则规定查字的时候怎么比大小、怎么排先后两个重要结论一个字符集对应多条校验规则不能跨字符集乱搭配建库时只写字符集不写校验规则MySQL会自动选用该字符集的默认校验规则-- 这两句效果完全一样createdatabased2charsetutf8;createdatabased2charsetutf8collateutf8_general_ci;2.4 实战选型建议业务场景推荐组合理由普通业务商品名、用户昵称、文章内容utf8mb4 utf8mb4_general_ci不区分大小写用户体验好支持全量字符敏感场景用户名、验证码、密钥、文件路径utf8mb4 utf8mb4_bin大小写严格区分精确匹配2.5 常用查看命令-- 查看当前选中数据库的字符集showvariableslikecharacter_set_database;-- 查看当前选中数据库的校验规则showvariableslikecollation_database;-- 查看系统支持的所有字符集showcharset;-- 查看系统支持的所有校验规则showcollation;-- 查看某个数据库的完整创建信息字符集校验规则一起看showcreatedatabasedb1;⚠️ 踩坑提醒刚登录MySQL还没use任何库时查询出来的character_set_database是系统默认回退值不代表某个具体库的配置。很多人默认装完MySQL查出来是latin1就是这个原因——这也是老版本MySQL中文乱码的万恶之源。2.6 底层视角数据库在磁盘上长啥样在Linux环境下MySQL的数据库本质就是数据目录下的一个文件夹。默认数据目录在/var/lib/mysql你创建的每一个数据库都会在这里生成一个同名文件夹。进入文件夹里面有一个db.opt文件MySQL 5.7及之前版本才有8.0已移除直接保存了这个库的字符集和校验规则。# 进入MySQL数据目录cd/var/lib/mysql# 查看所有数据库对应的文件夹ll# 查看db1库的配置文件catdb1/db.opt输出示例default-character-setutf8 default-collationutf8_general_ci这和你在MySQL里执行show create database db1;看到的信息完全一致。 保命提醒只允许查看禁止手动修改或删除这个文件底层文件由MySQL进程管理手动篡改极易损坏数据库。修改库配置请老老实实使用alter databaseSQL命令。三、仓库日常运维查看、修改、删除3.1 查看所有数据库想知道MySQL“园区”里一共有多少个仓库showdatabases;执行完会列出所有数据库包括系统自带的mysql、information_schema、performance_schema等系统库。3.2 查看数据库的“出生证明”想知道某个数据库的创建细节、用了什么字符集showcreatedatabasemytest;结果里两个容易被忽略的细节反引号数据库名用反引号包起来作用是防止库名刚好是SQL关键字/*!40100 ... */这不是注释是版本条件执行语法——MySQL版本大于4.01.00才会执行里面的语句用来兼容老版本3.3 修改数据库配置仓库建好了想改字符集用alter databasealterdatabasemytestdefaultcharactersetgbk;⚠️ 非常重要的提醒修改数据库字符集只对后续新建的表生效数据库里已经存在的表字符集不会跟着自动变。要改已有表的字符集还得单独执行修改表的语句。3.4 删除数据库高危操作dropdatabaseifexistsdb_name;执行之后整个数据库连带里面所有的表、数据都会被永久删除而且没有回收站、没有撤销键。 新手保命守则永远不要在生产环境随便执行drop database一定要加if exists避免删不存在的库导致报错删库之前千万、务必、一定要先备份四、备份与恢复数据安全的最后一道防线做开发的谁还没误删过数据备份就是你的后悔药。MySQL自带了mysqldump工具用来做逻辑备份。4.1 备份整个数据库注意mysqldump是在**系统终端命令行**执行的不是在MySQL客户端里面执行# 格式mysqldump -P端口 -u用户名 -p密码 -B 数据库名 备份文件路径mysqldump-P3306-uroot-p123456-BmytestD:/mytest.sql-B参数非常关键它会在备份文件里自动加上create database语句恢复的时候不用自己先建空库。 安全小提示正式环境不建议把密码明文写在命令里可以只写-p不写密码执行后会提示交互式输入避免密码泄露。4.2 恢复数据库恢复需要先进入MySQL命令行然后用source命令加载备份文件source D:/mytest.sql;4.3 进阶备份玩法只备份某几张表mysqldump-uroot-p数据库名 表名1 表名2D:/mytest_tables.sql同时备份多个数据库mysqldump-uroot-p-B库1 库2 库3D:/multi_db.sql 踩坑提醒如果备份的时候没加-B参数恢复的时候必须先手动创建一个空数据库然后use选中这个库再执行source否则会报错。五、连接监控看看谁在“逛”你的仓库觉得数据库突然变慢了怀疑有人偷偷连你的库一条命令查看所有当前连接showprocesslist;结果会显示每个连接的ID、用户、来源主机、正在执行的命令、运行时间等信息。如果看到陌生的IP和用户那你的库可能已经被入侵了六、新手最佳实践总结建库必指定字符集统一使用utf8mb4从根源告别乱码校验规则选通用普通业务场景utf8mb4_general_ci足够用删库前先备份永远给自己留一条后路生产环境严控权限不要随便给普通账号删库权限定期自动化备份手动备份靠不住脚本定时才靠谱拼写错误别慌张1064语法报错先查关键字拼写和标点好了MySQL库的基本操作就讲到这里。别看这些命令简单它们是后面所有表操作、数据操作的基础。下一篇咱们接着聊仓库里的“货架”——数据表的创建与管理。
MySQL数据库：库操作
文章目录MySQL入门保姆级教程库操作从入门到不删库跑路一、创建仓库create database 语法全解1.1 最简版一键建仓1.2 进阶版指定仓库的“语言配置”二、深挖字符集与校验规则别等乱码才后悔2.1 字符集管「能存什么文字」2.2 校验规则管「怎么对比和排序」场景1不区分大小写utf8_general_ci场景2区分大小写utf8_bin2.3 两者的关系2.4 实战选型建议2.5 常用查看命令2.6 底层视角数据库在磁盘上长啥样三、仓库日常运维查看、修改、删除3.1 查看所有数据库3.2 查看数据库的“出生证明”3.3 修改数据库配置3.4 删除数据库高危操作四、备份与恢复数据安全的最后一道防线4.1 备份整个数据库4.2 恢复数据库4.3 进阶备份玩法五、连接监控看看谁在“逛”你的仓库六、新手最佳实践总结MySQL入门保姆级教程库操作从入门到不删库跑路注本文所有 SQL 语句统一采用小写书写。MySQL 语法本身对关键字大小写不敏感大写小写执行效果完全一致小写写法更贴近日常敲代码的手写习惯读起来也更清爽个人练习用完全没问题。各位初学MySQL的小伙伴咱们今天聊最基础的「库操作」。你可以把MySQL想象成一个超大的物流园区每个数据库就是园区里的一个独立仓库——你要先有仓库才能往里面放货架数据表、堆货物数据。今天就手把手教你怎么建仓库、改仓库、备份仓库以及怎么避免“删库跑路”的经典悲剧。一、创建仓库create database 语法全解1.1 最简版一键建仓最基础的创建语句非常简单一行搞定createdatabasedb1;执行完你就拥有了一个叫db1的仓库。但这里有个坑如果这个名字的仓库已经存在再执行这句就会直接报错中断。所以稳妥起见建议加上「防重复保险」if not existscreatedatabaseifnotexistsdb1;它的意思很直白有就算了没有再建绝不报错。 新手高频报错提示遇到ERROR 1064 (42000)语法错误先看报错信息里near xxx指向的位置90%都是单词拼写错误比如把database少写一个 t 拼成dabase、少逗号、少分号这类低级失误。1.2 进阶版指定仓库的“语言配置”建仓库的时候我们可以指定两个核心配置字符集character set决定仓库能存什么语言的“货物”比如中文、英文、表情符号校验规则collate决定仓库盘点、查找货物时的“比对规则”比如区分不区分大小写 新手必踩坑预警很多教程里写的utf8在MySQL里并不是真正的UTF-8它实际叫utf8mb3最多只支持3字节字符存不了emoji、生僻汉字。业界标准做法是使用utf8mb4这才是完整的4字节UTF-8实现。标准的建库语句推荐这么写-- 创建使用utf8mb4字符集的数据库db2createdatabasedb2defaultcharactersetutf8mb4;-- 创建使用utf8mb4字符集 通用不区分大小写校验的数据库db3createdatabasedb3defaultcharactersetutf8mb4defaultcollateutf8mb4_general_ci;小知识点charsetutf8mb4和character set utf8mb4写法完全等价前者是简写后者是完整关键词效果一模一样。二、深挖字符集与校验规则别等乱码才后悔很多新手会把这俩概念混在一起其实它们分工完全不同一个管“存”一个管“比”。2.1 字符集管「能存什么文字」计算机底层只认识0和1字符集就是一张翻译对照表存数据的时候把文字转成二进制读数据的时候把二进制转回文字。字符集选错最直接的后果就是乱码latin1只能存英文、西欧字母存中文直接变成问号???utf8MySQL特有阉割版3字节UTF-8支持常用中文但存不了emoji表情、生僻汉字utf8mb4完整的4字节UTF-8中文、emoji、生僻字全能存新项目统一用这个一句话总结字符集决定了数据库里能存哪些文字。2.2 校验规则管「怎么对比和排序」校验规则必须依附于字符集存在一个字符集可以配套很多套校验规则。它管的是文字之间怎么判断相等、怎么排序。最常见的就两类效果天差地别后缀_ciCase Insensitive不区分大小写后缀_binBinary二进制精确比对区分大小写咱们用实际例子直观感受一下区别场景1不区分大小写utf8_general_cicreatedatabasetest1collateutf8_general_ci;usetest1;createtableperson(namevarchar(20));insertintopersonvalues(a),(A),(b),(B);查询nameaselect*frompersonwherenamea;结果a和A都出来了系统认为大小写是同一个值。排序效果select*frompersonorderbyname;结果A, a, B, b大小写字母排在一起。场景2区分大小写utf8_bincreatedatabasetest2collateutf8_bin;usetest2;createtableperson(namevarchar(20));insertintopersonvalues(a),(A),(b),(B);同样查询nameaselect*frompersonwherenamea;结果只返回a大小写严格区分完全精确匹配。排序效果select*frompersonorderbyname;结果A, B, a, b所有大写字母按ASCII码排在小写前面。2.3 两者的关系打个比方字符集 一本字典规定了里面有哪些字校验规则 这本字典的排序规则规定查字的时候怎么比大小、怎么排先后两个重要结论一个字符集对应多条校验规则不能跨字符集乱搭配建库时只写字符集不写校验规则MySQL会自动选用该字符集的默认校验规则-- 这两句效果完全一样createdatabased2charsetutf8;createdatabased2charsetutf8collateutf8_general_ci;2.4 实战选型建议业务场景推荐组合理由普通业务商品名、用户昵称、文章内容utf8mb4 utf8mb4_general_ci不区分大小写用户体验好支持全量字符敏感场景用户名、验证码、密钥、文件路径utf8mb4 utf8mb4_bin大小写严格区分精确匹配2.5 常用查看命令-- 查看当前选中数据库的字符集showvariableslikecharacter_set_database;-- 查看当前选中数据库的校验规则showvariableslikecollation_database;-- 查看系统支持的所有字符集showcharset;-- 查看系统支持的所有校验规则showcollation;-- 查看某个数据库的完整创建信息字符集校验规则一起看showcreatedatabasedb1;⚠️ 踩坑提醒刚登录MySQL还没use任何库时查询出来的character_set_database是系统默认回退值不代表某个具体库的配置。很多人默认装完MySQL查出来是latin1就是这个原因——这也是老版本MySQL中文乱码的万恶之源。2.6 底层视角数据库在磁盘上长啥样在Linux环境下MySQL的数据库本质就是数据目录下的一个文件夹。默认数据目录在/var/lib/mysql你创建的每一个数据库都会在这里生成一个同名文件夹。进入文件夹里面有一个db.opt文件MySQL 5.7及之前版本才有8.0已移除直接保存了这个库的字符集和校验规则。# 进入MySQL数据目录cd/var/lib/mysql# 查看所有数据库对应的文件夹ll# 查看db1库的配置文件catdb1/db.opt输出示例default-character-setutf8 default-collationutf8_general_ci这和你在MySQL里执行show create database db1;看到的信息完全一致。 保命提醒只允许查看禁止手动修改或删除这个文件底层文件由MySQL进程管理手动篡改极易损坏数据库。修改库配置请老老实实使用alter databaseSQL命令。三、仓库日常运维查看、修改、删除3.1 查看所有数据库想知道MySQL“园区”里一共有多少个仓库showdatabases;执行完会列出所有数据库包括系统自带的mysql、information_schema、performance_schema等系统库。3.2 查看数据库的“出生证明”想知道某个数据库的创建细节、用了什么字符集showcreatedatabasemytest;结果里两个容易被忽略的细节反引号数据库名用反引号包起来作用是防止库名刚好是SQL关键字/*!40100 ... */这不是注释是版本条件执行语法——MySQL版本大于4.01.00才会执行里面的语句用来兼容老版本3.3 修改数据库配置仓库建好了想改字符集用alter databasealterdatabasemytestdefaultcharactersetgbk;⚠️ 非常重要的提醒修改数据库字符集只对后续新建的表生效数据库里已经存在的表字符集不会跟着自动变。要改已有表的字符集还得单独执行修改表的语句。3.4 删除数据库高危操作dropdatabaseifexistsdb_name;执行之后整个数据库连带里面所有的表、数据都会被永久删除而且没有回收站、没有撤销键。 新手保命守则永远不要在生产环境随便执行drop database一定要加if exists避免删不存在的库导致报错删库之前千万、务必、一定要先备份四、备份与恢复数据安全的最后一道防线做开发的谁还没误删过数据备份就是你的后悔药。MySQL自带了mysqldump工具用来做逻辑备份。4.1 备份整个数据库注意mysqldump是在**系统终端命令行**执行的不是在MySQL客户端里面执行# 格式mysqldump -P端口 -u用户名 -p密码 -B 数据库名 备份文件路径mysqldump-P3306-uroot-p123456-BmytestD:/mytest.sql-B参数非常关键它会在备份文件里自动加上create database语句恢复的时候不用自己先建空库。 安全小提示正式环境不建议把密码明文写在命令里可以只写-p不写密码执行后会提示交互式输入避免密码泄露。4.2 恢复数据库恢复需要先进入MySQL命令行然后用source命令加载备份文件source D:/mytest.sql;4.3 进阶备份玩法只备份某几张表mysqldump-uroot-p数据库名 表名1 表名2D:/mytest_tables.sql同时备份多个数据库mysqldump-uroot-p-B库1 库2 库3D:/multi_db.sql 踩坑提醒如果备份的时候没加-B参数恢复的时候必须先手动创建一个空数据库然后use选中这个库再执行source否则会报错。五、连接监控看看谁在“逛”你的仓库觉得数据库突然变慢了怀疑有人偷偷连你的库一条命令查看所有当前连接showprocesslist;结果会显示每个连接的ID、用户、来源主机、正在执行的命令、运行时间等信息。如果看到陌生的IP和用户那你的库可能已经被入侵了六、新手最佳实践总结建库必指定字符集统一使用utf8mb4从根源告别乱码校验规则选通用普通业务场景utf8mb4_general_ci足够用删库前先备份永远给自己留一条后路生产环境严控权限不要随便给普通账号删库权限定期自动化备份手动备份靠不住脚本定时才靠谱拼写错误别慌张1064语法报错先查关键字拼写和标点好了MySQL库的基本操作就讲到这里。别看这些命令简单它们是后面所有表操作、数据操作的基础。下一篇咱们接着聊仓库里的“货架”——数据表的创建与管理。
