Lehman-Blog - DB
type: channel
link: https://blog.lehman.top/category/DB/
description: MySQL/Oracle/Microsoft SQL Server/Redis
items: MySQL知识扩展：sql_mode
items: PostgreSQL常用问题汇总
items: PostgreSQL常用功能汇总
items: PostgreSQL-DDL/DML/DCL/DQL
items: 数据唯一ID：自增ID、UUID、雪花ID
items: Redis安装和使用
items: MYSQL-DDL/DML/DCL/DQL
items: MYSQL常用优化技巧
items: MYSQL常见问题汇总
items: MYSQL常用功能汇总

MySQL知识扩展：sql_mode
type: item
date: 2025-05-28T10:45:00+08:00
description: 前言最近公司开发同学A在群聊中说某某系统的某条SQL有问题，抛出异常，而同样的SQL在开发同学B（SQL由B同学编写）的环境没有问题，带着好奇心我上前协助一看，最终发现异常大致内容是：sql_mode=ONLY_FULL_GROUP_BY，SQL大致类似如下格式：SELECT name, age, SUM(sales) FROM orders GROUP BY name; 此时让我想起了年轻的自己，当初也犯过如此问题，于是我耐心的告知开发同学B：SELECT 中的非聚合字段必须出现在 GROUP BY 子句中所有非聚合字段需通过聚合函数（如MAX、MIN、SUM）处理，或显式声明分组依据解释完毕后，开发同学B提出疑问，为什么开发同学A的环境不行，而我的就可以，于是我在他们电脑分别使用了SELECT VERSION(); 语句进行查看版本，然后告知开发同学A和开发同学B，自MySQL 5.7.5 起：官方将ONLY_FULL_GROUP_BY 加入默认的sql_mode，以增强 SQL 的标准兼容性和数据一致性，当我说完 打算扬长而去时，两位开发同学随即又抛出疑问：sql_mode是什么？我：......简介MySQL为了支持在不同的环境下运行，允许我们给它设置不同的运行模式（sql_mode）sql_mode是MySQL中用于设置sql语法和行为的系统变量控制MySQL的sql解析和执行的方式，使其与sql标准或其他数据库系统的行为一致，通过设置sql_mode，可以改变MySQL处理待定sql操作的方式[scode type="green"]我们在安装mysql时就要确定它的运行模式（sql_mode），不建议在中途更改它的运行模式[/scode][scode type="green"]建议在跨mysql实例备份还原数据时，保持双方mysql实例具有相同的运行模式[/scode]sql_mode模式MySQL 支持的 sql_mode 模式模式名含义STRICT_TRANS_TABLES事务表中对无效数据报错，非事务表警告STRICT_ALL_TABLES所有表对无效数据都报错NO_ZERO_IN_DATE不允许日期中的月或日为 0（如 2023-00-10）NO_ZERO_DATE不允许日期为 '0000-00-00'ERROR_FOR_DIVISION_BY_ZERO除以 0 报错NO_AUTO_CREATE_USER禁止 GRANT 自动创建用户（MySQL 8.0 移除）NO_ENGINE_SUBSTITUTION禁止在指定引擎不可用时自动替代ONLY_FULL_GROUP_BYGROUP BY 中必须列出所有非聚合列ANSI_QUOTES用双引号表示标识符（字段、表名等）PIPES_AS_CONCAT使用 “||” 表示字符串拼接IGNORE_SPACE函数名后允许加空格ANSI启用符合 ANSI SQL 的语法和行为TRADITIONAL启用接近标准 SQL 的严格模式组合NO_BACKSLASH_ESCAPES禁用反斜杠 “\” 作为转义字符HIGH_NOT_PRECEDENCE改变 “NOT” 运算符的优先级NO_UNSIGNED_SUBTRACTION无符号数减法结果转换为有符号类型REAL_AS_FLOAT将 “REAL” 类型作为 “FLOAT” 处理PAD_CHAR_TO_FULL_LENGTH使用 “CHAR” 比较时右侧填充空格至固定长度ALLOW_INVALID_DATES允许插入无效日期（非严格模式时有效）TIME_TRUNCATE_FRACTIONAL时间字段截断小数秒部分而非四舍五入sql_mode对比对比项MySQL 5.7 默认值MySQL 8.0 默认值默认 sql_modeONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTIONNO_AUTO_CREATE_USER默认启用已移除（不再支持 GRANT 创建用户）严格性中等更严格，接近 SQL 标准查看和修改sql_modesql_mode，分为全局的、会话的（即：针对当前链接有效），在mysql运行时查看和更改sql_mode的sql如下：-- 查看当前会话的sql_mode selec…

PostgreSQL常用问题汇总
type: item
date: 2024-07-11T17:31:55+08:00

PostgreSQL常用功能汇总
type: item
date: 2024-07-11T17:31:02+08:00

PostgreSQL-DDL/DML/DCL/DQL
type: item
date: 2024-07-11T17:23:12+08:00
description: [scode type="red"]以下示例基于PostgreSQL[/scode][collapse status="false" title="常用DDL"]DDLDDL：[Data Mefinition Language] 数据定义语言关键点： CREATE TABLE、ALTER TABLE、DROP TABLE、CREATE/DROP INDEX 等主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用DDL操作是隐性提交的！不能rollback [scode type="blue"]创建表[/scode]CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), birth_date DATE, hire_date DATE ); [scode type="blue"]修改表[/scode]#添加列 ALTER TABLE employees ADD COLUMN email VARCHAR(100); #修改列类型 ALTER TABLE employees ALTER COLUMN birth_date TYPE TIMESTAMP; #删除列 ALTER TABLE employees DROP COLUMN email; [scode type="blue"]删除表[/scode]DROP TABLE employees; [scode type="blue"]创建视图[/scode]CREATE VIEW employee_names AS SELECT first_name, last_name FROM employees; [scode type="blue"]删除视图[/scode]DROP VIEW employee_names; [/collapse][collapse status="false" title="常用DML"]DMLDML：[Data Manipulation Language] 数据操纵语言关键点： INSERT、UPDATE、DELETE主要是对表数据进行操作，如对表数据进行增、删、改[scode type="blue"]插入数据[/scode]#插入单行数据 INSERT INTO employees (first_name, last_name, birth_date, hire_date) VALUES ('John', 'Doe', '1990-01-01', '2020-01-01'); #插入多行数据 INSERT INTO employees (first_name, last_name, birth_date, hire_date) VALUES ('Jane', 'Doe', '1992-02-02', '2021-02-02'), ('Jim', 'Beam', '1985-03-03', '2019-03-03'); [scode type="blue"]更新数据[/scode]#更新单个字段 UPDATE employees SET hire_date = '2021-01-01' WHERE employee_id = 1; #更新多个字段 UPDATE employees SET first_name = 'Jonathan', last_name = 'Smith' WHERE employee_id = 2; [scode type="blue"]删除数据[/scode]#删除符合条件的行 DELETE FROM employees WHERE employee_id = 3; #删除所有数据（但保留表结构） DELETE FROM employees; [/collapse][collapse status="false" title="常用DCL"]DCLDCL： [Data Control Language] 数据控制语言关键点： GRANT、REVOKE主要主要用于控制数据库的访问权限和安全性[scode type="blue"]授予权限[/scode]#授予用户对表的全部权限 GRANT ALL PRIVILEGES ON TABLE employees TO user_name; #授予用户对表的特定权限 GRANT SELECT, INSERT, UPDATE ON TABLE employees TO user_name; #授予角色权限 GRANT SELECT ON TABLE employees TO role_nam…

数据唯一ID：自增ID、UUID、雪花ID
type: item
date: 2024-05-10T10:43:00+08:00
description: 前言在数据时代，无论是单机环境还是分布式环境，都需要保持数据ID的唯一性针对常见的唯一ID，一般有三种，分别是：自增ID、UUID和雪花ID自增ID自增ID（Auto-increment ID）是一种在数据库中常用的主键生成方法，通常用于唯一标识数据库表中的每一行数据优点唯一性：自增ID保证了每个表中的行都有唯一的标识符，避免了重复数据的问题。简单性：自增ID是一种简单且直观的主键生成方法，易于实现和维护。性能：自增ID可以提高数据库的性能，因为数据库引擎会自动管理并优化自增ID的索引，使数据检索更快速。插入效率：自增ID的顺序插入可以减少数据页的分裂，提高插入效率。隐私性：自增ID不透露关于数据的任何信息，可以保护数据的隐私性。缺点可预测性：自增ID是连续的数字序列，可能暴露了数据库中数据的规律性，存在安全风险。分布式环境下的问题：在分布式系统中，自增ID的生成可能存在冲突和同步问题，需要额外的处理来确保唯一性。删除数据后的空洞：当删除表中的数据时，可能会留下空洞，导致ID不再是连续的。数据迁移问题：在数据迁移或合并时，可能需要额外考虑自增ID的处理，避免冲突或重复。只适合单机数据库，不能用于分库分表的情况，会产生重复id。UUIDUUID（Universally Unique Identifier）是一种用于标识信息的唯一标识符，拥有随机、无序，具有非常好的全局唯一性优点全局唯一性：UUID是全局唯一的标识符，几乎可以保证在不同系统和数据库中生成的UUID都是唯一的。分布式系统支持：在分布式系统中，UUID可以在不同节点上生成唯一的标识符，避免了冲突。随机性：UUID是基于时间和随机数生成的，不易被猜测，提高了安全性。不依赖中心化机制：生成UUID不需要中心化的机制，每个节点都可以独立生成唯一的标识符。无序性：UUID是无序的，不会暴露数据之间的关系，保护了数据的隐私性。缺点长度：UUID相对于自增ID来说比较长（128位），在存储和索引时占用的空间较大。可读性：UUID是一串十六进制数字和字符，不如自增ID那样直观和易读。性能：生成UUID的算法相对复杂，可能会影响性能，尤其在高并发环境下。不适用于某些场景：在某些情况下，需要连续递增的ID，UUID并不适合。数据库索引问题：由于UUID的无序性，可能会导致索引效率下降，需要额外的处理来优化索引。雪花ID有这样一种说法，自然界中并不存在两片完全一样的雪花(世界上没有两片相同的树叶)，每一片雪花都拥有自己漂亮独特的形状且是独一无二。雪花算法也表示生成的 ID 如雪花般独一无二，是一种用于生成分布式系统中唯一ID的算法，由Twitter开发。它的核心思想是将一个64位的ID分成不同的部分，每部分表示不同的信息，例如时间戳、机器ID和序列号。雪花算法满足唯一性和有序性，避免了分布式系统环境下的ID冲突；并且生成ID的过程中无需依赖数据库等外部系统，减少了系统复杂性。[scode type="green"]SnowFlake 算法结构如下：大致分为了无效位、时间位、机器位和序列号位[/scode]1）第一位 占用1bit，其值始终是0，没有实际作用（因为二进制中最高位是符号位，1表示负数，0表示正数。生成的id一般都是用整数，所以最高位固定为0）。2）时间戳 占用41bit，精确到毫秒，总共可以容纳约69年的时间。3）工作机器id 占用10bit，其中高位5bit是数据中心ID，低位5bit是工作节点ID，做多可以容纳1024个节点。4）序列号 占用12bit，每个节点每毫秒0开始不断累加，最多可以累加到4095，一共可以产生4096个ID。那么SnowFlake算法在同一毫秒内最多可以生成多少个全局唯一ID呢？同一毫秒的ID数量 = 1024 X 4096 = 4194304优点全局唯一性：雪花算法生成的ID在分布式系统中保证全局唯一，避免了ID冲突的问题。分布式系统支持：雪花算法适用于分布式系统，每个节点可以独立生成唯一的ID，无需中心化的管理。高性能：雪花算法生成ID的速度较快，适用于高并发的场景。简单：相对于一些复杂的ID生成方案，雪花算法相对简单易懂，易于实现和维护。时间有序：雪花算法生成的ID中包含时间戳，可以保证生成的ID在一定程度上是有序的。缺点时钟回拨问题：如果系统时钟发生回拨，可能会导致生成的ID重复，需要额外处理时钟同步的问题。依赖机器ID：雪花算法中需要分配机器ID，如果分配不当或者机器ID冲突，可能会导致ID冲突。单点故障：如果分配的机器ID集中在少数几台机器上，这些机器出现故障可能会影响整个系统的ID生成。有序性：虽然时间戳可以保证一定的有序性，但在高并发场景下，可能会出现ID递增速度过快导致性能问题。长度固定：雪花算法生成的ID长度固定，可能会占用较…

Redis安装和使用
type: item
date: 2023-06-06T20:18:00+08:00
description: Windows系统[scode type="green"]下载[/scode][scode type="share"]Redis 官方网站现在没有提供 Windows 版的安装包（Redis-x64-3.0.504之后不再提供）[/scode]以下有三种方案可供选择1.下述Windows 安装包是某位民间“大神”根据 Redis 源码改造的，并非 Redis 官方网站提供[button color="success" icon="glyphicon glyphicon-arrow-right" url="https://github.com/tporadowski/redis/releases" type=""]Github传送门[/button]2.使用微软维护版（Redis-x64-3.0.504）[button color="success" icon="glyphicon glyphicon-arrow-right" url="https://github.com/MicrosoftArchive/redis/releases" type=""]Github传送门[/button]3.安装虚拟机或根据官网提示安装WSL2（适用于 Linux 的 Windows 子系统）WSL2 允许您在 Windows 上本地运行 Linux 二进制文件。要使用此方法，您需要运行 Windows 10 2004 版及更高版本或 Windows 11[scode type="green"]安装[/scode]选择“添加Redis目录到环境变量PATH中”，这样方便系统自动识别Redis执行文件在哪里（可手动配置）端口号可保持默认的6379，并选择防火墙例外，从而保证外部可以正常访问Redis服务设定redis内存使用最大值打开Redis服务配置文件（注意：不要找错了，通常为redis.windows-service.conf，而不是redis.windows.conf。后者是以非系统服务方式启动程序使用的配置文件）找到含有requirepass字样的地方，追加一行，输入requirepass 123456。这是访问Redis时所需的密码，一般测试情况下可以不用设定密码点击“开始”>右击“计算机”>选择“管理”。在左侧栏中依次找到并点击“计算机管理（本地）”>服务和应用程序>服务。再在右侧找到Redis名称的服务，查看启动情况。如未启动，则手动启动之。正常情况下，服务应该正常启动并运行了（没有服务的的话见下面tips）启动Redis客户端redis-cli -h ip地址 -p 端口号 -n 数据库编号 -a 密码 关闭Redis服务redis-server --service-stop redis-cli shutdown 检查是否连接成功（测试客户端和服务端是否成功连接。输出PING命令，若返回PONG则证明成功连接。）其他命令redis-cli -h ip地址 -p 端口号 -n 数据库编号 -a 密码 输入了数据库编码，连接数据库执行如下命令： # 切换数据库 select 数据库编号 # 清空redis数据库 flushall # 清理当前db数据 flushdb [scode type="blue"]tips[/scode]如果计算机服务内没有Redis的服务,我们可以手动创建1.注册Redis服务（通过 CMD 命令行工具进入 Redis 安装目录，将 Redis 服务注册到 Windows 服务中，执行以下命令）redis-server.exe --service-install redis.windows.conf --loglevel verbose 2.执行完后，得到以下类似输出，说明注册成功[1868] 07 Jan 15:00:08.223 # Granting read/write access to 'NT AUTHORITY\NetworkService' on: "D:\Redis-x64-5.0.10" "D:\Redis-x64-5.0.10\" [1868] 07 Jan 15:00:08.230 # Redis successfully installed as a service. 3.启动Redis服务redis-server --service-start redis-server redis.windows.conf # 通过配置服务启动 Mac 系统使用 Homebrew 在 macOS 上安装 Redis[scode type="red"]安装前置包管理器：Homebrew（目前比较流行的包管理器）[/scode][button color="success" icon="glyphi…

MYSQL-DDL/DML/DCL/DQL
type: item
date: 2020-11-21T15:20:00+08:00
description: [scode type="red"]以下示例基于MYSQL[/scode][collapse status="false" title="常用DDL"]DDLDDL：[Data Mefinition Language] 数据定义语言关键点： CREATE TABLE、ALTER TABLE、DROP TABLE、CREATE/DROP INDEX 等主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用DDL操作是隐性提交的！不能rollback [scode type="blue"]创建数据库[/scode] # 创建 user 数据库 CREATE DATABASE user; [scode type="blue"]删除数据库[/scode] # 删除user数据库 DROP DATABASE user; [scode type="blue"] 创建表[/scode] # 语法： CREATE TABLE [IF NOT EXISTS] 表名( 字段名 字段类型 【字段约束】, 字段名 字段类型 【字段约束】, 字段名 字段类型 【字段约束】 ); # 案例：创建一张表【没有添加约束】 CREATE TABLE IF NOT EXISTS stuinfo ( stuid INT, stuname VARCHAR (20), stugender CHAR(1), email VARCHAR (20), borndate DATETIME ) ; -- 案例： DROP TABLE IF EXISTS stuinfo; CREATE TABLE IF NOT EXISTS stuinfo( stuid INT PRIMARY KEY, --添加了 主键约束 stuname VARCHAR(20) UNIQUE NOT NULL, -- 添加了 唯一约束+非空 stugender CHAR(1) DEFAULT '男', -- 添加了默认约束 email VARCHAR(20) NOT NULL, age INT CHECK( age BETWEEN 0 AND 100), -- 添加了检查约束，mysql不支持 majorid INT, CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major(id) -- 添加了外键约束-- majorid列 与 major表内的id列 ); [scode type="blue"]修改表[/scode] # 语法： ALTER TABLE 表名 ADD|CHANGE|MODIFY|DROP COLUMN 字段名 字段类型 【字段约束】 # 1，修改表名 ALTER TABLE stuinfo RENAME TO students ; # 2.添加字段 ALTER TABLE students ADD COLUMN borndate TIMESTAMP NOT NULL ; # 3.修改字段名 ALTER TABLE students CHANGE COLUMN borndate birthday DATETIME NULL ; # 4.修改字段类型 ALTER TABLE students MODIFY COLUMN birthday TIMESTAMP ; # 5.删除字段 ALTER TABLE students DROP COLUMN birthday ; [scode type="blue"]删除表[/scode] DROP TABLE IF EXISTS students ; [scode type="blue"] 复制表[/scode] # 复制表 CREATE TABLE newTable LIKE students ; # 复制表的结构+数据 CREATE TABLE newTable SELECT * FROM girls.`admin` #案例：复制employees表中的last_name，department_id，salary字段到新表 xx 表，但不复制数据 CREATE TABLE xx SELECT last_name, department_id, salary FROM myemployees.`employees` WHERE 1=2; # 恒不成立 [/collapse][collapse status="false" title="常用DML"]DMLDML：[Data Manipulation Language] 数据操纵语言关键点： INSERT、UPDATE、DELETE主要是对表数据进行操作，如对表数据进行增、删…

MYSQL常用优化技巧
type: item
date: 2020-11-21T14:00:00+08:00
description: 从15个方面，分享sql优化的一些小技巧避免使用select *很多时候，我们写sql语句时，为了方便，喜欢直接使用select *，一次性查出表中所有列的数据，例如:select * from user where id=1; 在实际业务场景中，可能我们真正需要使用的只有其中一两列。查了很多数据，但是不用，白白浪费了数据库资源，比如：内存或者cpu。此外，多查出来的数据，通过网络IO传输的过程中，也会增加数据传输的时间。还有一个最重要的问题是：select *不会走覆盖索引，会出现大量的回表操作，而从导致查询sql的性能很低。我们需要sql语句查询时，只查需要用到的列，多余的列根本无需查出来select name,age from user where id=1; 用union all代替union我们都知道sql语句使用union关键字后，可以获取排重后的数据。而如果使用union all关键字，可以获取所有数据，包含重复的数据。(select * from user where id=1) union (select * from user where id=2); 排重的过程需要遍历、排序和比较，它更耗时，更消耗cpu资源。所以如果能用union all的时候，尽量不用union。(select * from user where id=1) union all (select * from user where id=2); 小表驱动大表小表驱动大表，也就是说用小表的数据集驱动大表的数据集。假如有order和user两张表，其中order表有10000条数据，而user表有100条数据。这时如果想查一下，所有有效的用户下过的订单列表。可以使用in关键字实现：select * from order where user_id in (select id from user where status=1) 也可以使用exists关键字实现：select * from order where exists (select 1 from user where order.user_id = user.id and status=1) 前面提到的这种业务场景，使用in关键字去实现业务需求，更加合适。为什么呢？因为如果sql语句中包含了in关键字，则它会优先执行in里面的子查询语句，然后再执行in外面的语句。如果in里面的数据量很少，作为条件查询速度更快。而如果sql语句中包含了exists关键字，它优先执行exists左边的语句（即主查询语句）。然后把它作为条件，去跟右边的语句匹配。如果匹配上，则可以查询出数据。如果匹配不上，数据就被过滤掉了。这个需求中，order表有10000条数据，而user表有100条数据。order表是大表，user表是小表。如果order表在左边，则用in关键字性能更好。总结：in 适用于左边大表，右边小表exists 适用于左边小表，右边大表不管是用in，还是exists关键字，其核心思想都是用小表驱动大表。批量操作有一批数据经过业务处理之后，需要插入数据for(Order order: list){ orderMapper.insert(order): } 然后在循环中逐条插入数据。insert into order(id,code,user_id) values(123,'001',100); 该操作需要多次请求数据库，才能完成这批数据的插入。应该优化为：orderMapper.insertBatch(list)； 提供一个批量插入数据的方法。insert into order(id,code,user_id) values(123,'001',100),(124,'002',100),(125,'003',101); 这样只需要远程请求一次数据库，sql性能会得到提升，数据量越多，提升越大。但需要注意的是，不建议一次批量操作太多的数据，如果数据太多数据库响应也会很慢。批量操作需要把握一个度，建议每批数据尽量控制在500以内。如果数据多于500，则分多批次处理。多用limit有时候，我们需要查询某些数据中的第一条，比如：查询某个用户下的第一个订单，想看看他第一次的首单时间select id, create_date from order where user_id=123 order by create_date asc; 根据用户id查询订单，按下单时间排序，先查出该用户所有的订单数据，得到一个订单集合。然后在代码中，获取第一个元素的数据，即首单的数据，就能获取首单时间。List<Order> list = orderMapper.getOrderList(); Order order = …

MYSQL常见问题汇总
type: item
date: 2020-11-21T13:56:00+08:00
description: [scode type="red"]基于MYSQL[/scode][collapse status="false" title="常见命令"]# root密码登录 mysql -u root -p # 指定ip地址密码登录 mysql -u root -h 192.168.1.1 -p # 查询用户和主机(以root身份) use mysql; select user,host from user; [/collapse][collapse status="false" title="远程主机无法访问"][scode type="yellow"]一般情况：修改host,然后问题解决[/scode]Host XXX is not allowed to connect to this MySQL server修改host# 以root身份登录 use mysql; # 查看host的状态 select host from user where user='root'; # 将Host设置为通配符% update user set host = '%' where user ='root'; # 刷新权限即可 flush privileges; [scode type="yellow"]特殊情况：修改host仍然未解决[/scode]1.3306端口冲突解决方法：# 打开命令行，查看3306端口 netstat -ano | findstr "3306" # 查看占用端口的程序 tasklist | findstr "9158" # 发现有两个mysql进程都占据了3306端口； # 可使用快捷键win+r,输入`services.msc`,在服务中关闭一个mysql或者直接使用命令杀掉一个进程（如杀掉9158进程） taskkill /pid 9158 # 然后问题解决 [/collapse][collapse status="false" title="忘记密码"][scode type="yellow"]windows环境[/scode]1.在mysql安装目录下,找到my.ini文件，打开文件，在[mysqld]后面任意行，加入skip-grant-tables；2.重启MySQL：使用快捷键win+r打开运行，输入services.msc,回车打开服务，找到mysql服务，然后右击选择重启MySQL；3.打开cmd命令行，输入mysql -u root -p,直接登录mysql;4.修改root密码use mysql; # 旧的修改语句 update user set password=password("你的新密码") where user="root"; # 使用旧的修改语句如果报错：Unknown column ‘password’ in ‘field list’，则使用下面语句： # 注：新版本的mysql对密码有要求，具体见附录 update mysql.user set authentication_string=password('root') where user='root' ; # 刷新权限 flush privileges; 5.再次打开my.ini文件，删除skip-grant-tables,然后重启mysql服务。[scode type="yellow"]linux环境[/scode]1.打开my.cnf文件，vi /etc/my.cnf，[mysqld]后面任意行，加入skip-grant-tables，用来跳过密码验证的过程；2.使用/etc/init.d/mysql restart(或可能需要使用/etc/init.d/mysqld restart)重启mysql服务；3.登录mysql,并修改密码，具体过程与windows环境一致；4.修改完成后再打开my.cnf文件，删除skip-grant-tables,然后重启mysql服务。[/collapse][collapse status="false" title="添加用户并授权"]# 以root用户登录mysql mysql -u root -p use mysql; # 添加只能本地访问的新用户 create user 'user'@'localhost' identified by 'user!123456'; # 添加外网ip可以访问的新用户 create user 'user'@'%' identified by 'user!123456'; # 用户创建完成后需要授权 flush privileges; # 基于业务的授权 grant all privileges on dbname.* to user@'%' identif…

MYSQL常用功能汇总
type: item
date: 2020-11-21T09:57:00+08:00
description: [collapse status="false" title="表生成Excel"]SELECT COLUMN_NAME 列名, COLUMN_TYPE 数据类型, DATA_TYPE 字段类型, CHARACTER_MAXIMUM_LENGTH 长度, IS_NULLABLE 是否为空, COLUMN_DEFAULT 默认值, COLUMN_COMMENT 备注 FROM INFORMATION_SCHEMA. COLUMNS WHERE table_schema = 'sxepp' AND table_name = 'sx_jc_car' sxepp为数据库名称，到时候只需要修改成你要导出表结构的数据库即可sx_jc_car为表名，到时候换成你要导出的表的名称table_name 如果不写的话，默认会查询出所有表中的数据，这样可能就分不清到底哪些字段是哪张表中的了，所以还是建议写上要导出的名名称[/collapse][collapse status="false" title="插入大量数据"]创建随机生成字符串函数delimiter $$ CREATE FUNCTION rand_string(n int) RETURNS varchar(255) begin declare chars_str varchar(100) default "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"; declare return_str varchar(255) default ""; declare i int default 0; while i < n do set return_str=concat(return_str,substring(chars_str,floor(1+rand()*62),1)); set i= i+1; end while; return return_str; end $$ delimiter ; 创建循环插入函数delimiter $$ CREATE PROCEDURE `insert_data`(IN n int) BEGIN DECLARE i INT DEFAULT 1; WHILE (i <= n ) DO INSERT into system_inventory (i_on,related_units,opening_date ) VALUEs (rand_string(20),FLOOR(RAND() * 100) ,now() ); set i=i+1; END WHILE; END $$ delimiter ; 执行插入函数call insert_data(10000); [/collapse][collapse status="false" title="递归查询"]查询子节点的函数 查询时 包含自身（示例）create function getChildrenByFolder(folderId INT) returns varchar(4000) BEGIN DECLARE temp VARCHAR(4000); DECLARE tempChild VARCHAR(4000); SET temp = ''; SET tempChild = CAST(folderId AS CHAR); WHILE tempChild IS NOT NULL DO SET temp = CONCAT(temp,',',tempChild); SELECT GROUP_CONCAT(folder_id) INTO tempChild FROM system_base_folder WHERE FIND_IN_SET(parent_id,tempChild) > 0; END WHILE; RETURN temp; END 使用函数 进行递归查询SELECT * FROM system_base_folder WHERE FIND_IN_SET(folder_id,getChildrenByFolder(4)); 查询父节点 查询的时候 包含自身（示例）CREATE FUNCTION `getParentByOrganizationr`(organizationId longtext) RETURNS longtext CHARSET utf8 BEGIN DECLARE temp longtext default ''; DECLARE tempParent longtext default organizationId; WHILE organizationId is not null do SET temp =…