分类 SQLserver 下的文章

以表中某字段中部分字符做为分组依据

作者: liziyu
时间: 2023-01-04
分类: Laravel,Webman,SQLserver
评论

下面以ORM为例演示：

$result = $this->model->query()
->selectRaw('date_format(field_a, "%Y") as date, count(id) as count')
->groupBy('date')
->pluck('count', 'date')
->toArray();
$years = [];
foreach($result as $key=>$val) {
     $years['year'][] = $key. ' 学年';
     $years['datas'][] = $val;
}

1、更新十分频繁的字段上不宜建立索引：因为更新操作会变更B+树，重建索引。这个过程是十分消耗数据库性能的。
2、区分度不大的字段上不宜建立索引：类似于性别这种区分度不大的字段，建立索引的意义不大。因为不能有效过滤数据，性能和全表扫描相当。另外返回数据的比例在30%以外的情况下，优化器不会选择使用索引。
3、业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。虽然唯一索引会影响insert速度，但是对于查询的速度提升是非常明显的。另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，在并发的情况下，依然有脏数据产生。
4、多表关联时，要保证关联字段上一定有索引。
5、创建索引时避免以下错误观念：索引越多越好，认为一个查询就需要建一个索引；宁缺勿滥，认为索引会消耗空间、严重拖慢更新和新增速度；抵制唯一索引，认为业务的唯一性一律需要在应用层通过“先查后插”方式解决；过早优化，在不了解系统的情况下就开始优化。

截取转载：https://zhuanlan.zhihu.com/p/61687047

MYSQL 并发连接数是多少？设置连接池技艺？

作者: liziyu
时间: 2022-08-12
分类: SQLserver
评论

MYSQL 能支持多少连接？

MYSQL的TCP连接支持长连接，所以每次操作完数据库，可以不必直接关掉连接，而是等待下次使用的时候在复用这个连接。所有的Socket长连接都是通过TCP自带的ping来维持心跳(TCP保活)，从而保持连接状态。

长连接的好处很多，自然大家都用长连接。慢慢就搞出一套长连接维护的工具 - 数据库连接池。

MYSQL 的最大连接数在5.7 - 8.x 版本中默认是151，最大可以达到16384（2^14）。如何设置最大连接数在于你的服务器性能，查看 MYSQL连接数信息命令：
show variables like 'max_connections';

查询当前数据库已建立连接数：
show status like 'Threads_connected';

连接池设置多少连接才合适？

设置连接池的大小肯定不是越大越好，需要考虑的是当前服务所在机器的性能，网络状况，数据库机器性能，数据库特性等等。

PostgreSQL提供了一个设置预期线程池大小的公式：
connections = ((core_count * 2) + effective_spindle_count)
其中，core_count是CPU核心， effective_spindle_count 的含义是有效主轴数，如果你的服务器使用的是带有16个磁盘的RAID，那么valid_spindle_count=16。它实质上是服务器可以管理多少个并行I / O请求的度量。

PHP strtotime()函数给指定时间戳/日期加固定时长

作者: liziyu
时间: 2022-08-02
分类: PHP,Server,SQLserver
评论

<?php
echo date('Y-m-d H:i:s',strtotime('now'));//当前时间戳 2017-01-09 21:04:11
echo date('Y-m-d H:i:s',strtotime('+1second'));//当前时间戳+1秒 2017-01-09 21:04:12
echo date('Y-m-d H:i:s',strtotime('+1minute'));//当前时间戳+1分 2017-01-09 21:05:11
echo date('Y-m-d H:i:s',strtotime('+1hour'));//当前时间戳+1小时 2017-01-09 22:04:11
echo date('Y-m-d H:i:s',strtotime('+1day'));//当前时间戳+1天 2017-01-10 21:04:11
echo date('Y-m-d H:i:s',strtotime('+1week'));//当前时间戳+1周 2017-01-16 21:04:11
echo date('Y-m-d H:i:s',strtotime('+1month'));//当前时间戳+1月 2017-02-09 21:04:11
echo date('Y-m-d H:i:s',strtotime('+1year'));//当前时间戳+1年 2018-01-09 21:04:11
echo date('Y-m-d H:i:s',strtotime('+12year 12month 12day 12hour 12minute 12second'));//当前时间戳+12年，12月，12天，12小时，12分，12秒 2030-01-22 09:16:23
$t=1483967416;//指定时间戳
echo $dt=date('Y-m-d H:i:s',$t);//2017-01-09 21:10:16
/*方法一*/
echo date('Y-m-d H:i:s',$t+1*24*60*60);//指定时间戳+1天 2017-01-10 21:10:16
echo date('Y-m-d H:i:s',$t+365*24*60*60);//指定时间戳+1年 2018-01-09 21:10:16
/*方法二*/
//$dt是指定时间戳格式化后的日期
echo date('Y-m-d H:i:s',strtotime("$dt+1day"));//指定时间戳+1天 2017-01-10 21:10:16
echo date('Y-m-d H:i:s',strtotime("$dt+1year"));//指定时间戳+1年 2018-01-09 21:10:16
/*方法三*/
//$t是指定时间戳
echo date('Y-m-d H:i:s',strtotime("+1day",$t));//指定时间戳+1天 2017-01-10 21:10:16
echo date('Y-m-d H:i:s',strtotime("+1year",$t));//指定时间戳+1年 2018-01-09 21:10:16
//指定时间戳加1月、1周、1小时、1分、1秒原理同上；

SQL语句分类有哪些？分别代表什么意思？

作者: liziyu
时间: 2022-02-20
分类: SQLserver
评论

1.数据查询语言（DQL）

数据查询语言（Data Query Language, DQL）保留字SELECT、FROM，WHERE，GROUP BY，HAVING和ORDER BY（查询操作）。

2.数据定义语言（DDL）

数据定义语言 (Data Definition Language, DDL) CREATE、ALTER、DROP三个语法所组成（对表结构操作）。

3.数据操纵语言（DML）

数据操纵语言（Data Manipulation Language, DML）INSERT、UPDATE、DELETE三种指令为核心（对表内数据操作）。

4.数据控制语言（DCL）

数据控制语言 (Data Control Language) 由 GRANT 和 REVOKE 两个指令组成（权限管理）。

5.指针控制语言（CCL）

它的语句，像DECLARE CURSOR、FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。

6.事务处理语言（TPL）

包括BEGIN TRANSACTION、COMMIT和ROLLBACK（事务控制）。

工作流后端数据表结构(workflow仿钉钉)

作者: liziyu
时间: 2022-02-09
分类: SQLserver
评论

可以根据它自研引擎

CREATE TABLE `departments` (
  `dept_id` int(11) NOT NULL COMMENT '部门id',
  `dept_name` varchar(255) NOT NULL COMMENT '部门名',
  `leader` varchar(255) DEFAULT NULL COMMENT '部门主管',
  `parent_id` int(11) DEFAULT NULL COMMENT '父部门id',
  `created` datetime DEFAULT NULL COMMENT '创建时间',
  `updated` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`dept_id`),
  KEY `dept_id` (`dept_id`),
  KEY `parent_id` (`parent_id`),
  KEY `leader` (`leader`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `form_groups` (
  `group_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `group_name` varchar(255) NOT NULL COMMENT '组名',
  `sort_num` int(3) NOT NULL DEFAULT '0' COMMENT '排序号',
  `created` datetime DEFAULT NULL COMMENT '创建时间',
  `updated` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`group_id`),
  UNIQUE KEY `group_name` (`group_name`),
  KEY `group_id` (`group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=90 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `process_instances` (
  `instance_id` varchar(40) NOT NULL COMMENT '审批实例ID',
  `title` varchar(255) DEFAULT NULL COMMENT '标题',
  `commit_user` varchar(50) DEFAULT NULL COMMENT '发起人ID',
  `forms` text COMMENT '表单内容',
  `status` varchar(10) DEFAULT NULL COMMENT '流程状态',
  `result` varchar(10) DEFAULT NULL COMMENT '流程结果',
  `instance_nodes` text COMMENT '流程审批链',
  `created` datetime DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  PRIMARY KEY (`instance_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `process_templates` (
  `template_id` varchar(50) NOT NULL DEFAULT '' COMMENT '审批摸板ID',
  `template_name` varchar(125) DEFAULT NULL COMMENT '摸板名称',
  `form_items` text COMMENT '摸板表单',
  `process` text COMMENT '流程数据',
  `icon` varchar(50) DEFAULT '' COMMENT '图标',
  `background` varchar(50) DEFAULT NULL COMMENT '图标背景色',
  `notify` varchar(255) DEFAULT NULL COMMENT '通知',
  `who_commit` varchar(1024) DEFAULT NULL COMMENT '谁能提交',
  `who_edit` varchar(1024) DEFAULT NULL COMMENT '谁能编辑',
  `who_export` varchar(1024) DEFAULT NULL COMMENT '谁能导出数据',
  `is_stop` bit(1) DEFAULT b'0' COMMENT '是否已停用',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  `created` datetime DEFAULT NULL COMMENT '创建时间',
  `updated` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`template_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `template_group` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `template_id` varchar(50) NOT NULL,
  `group_id` int(11) NOT NULL,
  `sort_num` int(11) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `template_id` (`template_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=167 DEFAULT CHARSET=utf8mb4;


CREATE TABLE `users` (
  `user_id` int(11) NOT NULL COMMENT '用户id',
  `user_name` varchar(255) NOT NULL COMMENT '用户名',
  `pingyin` varchar(50) DEFAULT NULL COMMENT '拼音  全拼及首字母缩写',
  `alisa` varchar(255) DEFAULT NULL COMMENT '昵称',
  `avatar` varchar(255) DEFAULT NULL COMMENT '头像',
  `sex` bit(1) DEFAULT b'1' COMMENT '性别',
  `department_ids` varchar(255) NOT NULL COMMENT '部门id，分隔',
  `entry_date` date DEFAULT NULL COMMENT '入职日期',
  `leave_date` date DEFAULT NULL COMMENT '离职日期',
  `admin` int(2) DEFAULT '2' COMMENT '管理级别 0=主管理员 1=子管理员 2=普通员工',
  `created` datetime DEFAULT NULL COMMENT '创建时间',
  `updated` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`user_id`),
  KEY `user_id` (`user_id`),
  KEY `department_ids` (`department_ids`),
  KEY `leave_date` (`leave_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

对应demo：
前端：https://github.com/willianfu/workflow-client
后端：https://github.com/willianfu/workflow-server

MySQL设计经验分享

作者: liziyu
时间: 2022-01-15
分类: SQLserver
评论

INT等整型不要采用无符号的，计算增加会减少时候如果是负数会报错。
自增用8个字节的BIG INT，而不要用四个字节INT，自增在MySQL 8.0前都会有回溯问题。
字符集用UTF8MB4字符编码。
日期类型用DATETIME，精确到毫秒用DATETIME(6),不建议使用INT或TIMESTAMP。
用JSON存储非频繁更新的结构化数据，比如用户标签等。
每张表一定要有一个主键，核心表要有全局唯一字段（比如雪花算法生成id或有序UUID）作为主键。
对日志，监控类的表，可以使用压缩设计，提升存储效率。推荐使用透明页压缩。
敏感字段要加密，动态盐+非固定加密算法（ AES256等）+多轮加密。
Mysql中通过KV方式访问表中数据，若只是简单的SET和GET，可以转成Memcached的KV方式，减少开销，提升50%左右性

管理后台系统权限设计（数据库）

作者: liziyu
时间: 2022-01-14
分类: Blog,SQLserver
评论

通过角色获取到用户 Roles-UserRoles-Users
通过角色获取到导航菜单 Roles-NavigationRoles-Navigations
通过角色获取到部门以及部门用户 Roles-RoleGroup-Groups-UserGroup-Users
通过角色获取到授权应用 Roles-RoleApp

这个算是比较经典的了，需要的可能参考。

Mysql workbench字段中有PK,NN,UQ,BIN,UN,ZF,AI字段类型的含义

作者: liziyu
时间: 2021-12-29
分类: SQLserver
评论

PK：primary key 主键
NN：not null 非空
UQ：unique 唯一索引
BIN：binary 二进制数据(比text更大的二进制数据)
UN：unsigned 无符号整数（非负数）
ZF：zero fill 填充0 例如字段内容是1 int(4), 则内容显示为0001
AI：auto increment 自增
G：generated column 生成列

在mysql查询语句中加`\g、\G`的意思：

\g 的作用是分号和在sql语句中写“；”是等效的
\G 的作用是将查到的结构旋转90度变成纵向（换行打印）