🗄️数据库

问题 业务中遇到一个非常慢的查询，大致可以简化为以下SQL： 1 2 3 4 5 select * from t1 where c1 not in (...) and c2 not like 'ABC%' and c3 in (...) and c4=-1 and c5>'2020-01-01,00:00:00' 其中： 有索引且命中的条件已简化掉了 c5为自定义格式的时间字符串 c2和c5有索引（普通索引），c1、c3和c4无索引 c3和c4为绑定条件 c1、c3和c4均为小值域列 分析 not in和not like会导致普通索引失效 为小值域列添加普通索引无法提供预期的效果 c3和c4为绑定条件，且条件固定不变，视为一体进行处理的话，可一定程度上扩充列的值域 c5虽然已有索引，但字符串比较操作还是太低效 解决方案 基于以上特殊的情形，搜索并尝试后发现，函数索引恰好可以达到预期，而使用的数据库又刚好支持（Oracel和PostgreSQL完全支持）。 添加索引 将c1的not in手动改为in查询，即可利用已有索引 c2的not like为后模糊查询，为其创建函数索引： 1 CREATE INDEX idx_t1_func_c2 ON t1(CASE WHEN c2 NOT LIKE 'ABC%' THEN 1 ELSE 0 END); 为c3和c4的绑定条件创建函数索引： 1 CREATE INDEX idx_t1_func_c3c4 ON t1(CASE WHEN c3 in (....

意外的乱码 在尝试用go操作pg数据库时，由于连接参数的笔误，意外得到了一条包含乱码的错误消息，很显然是字符编码的问题：win系统默认是gbk编码，debian一般都默认utf8编码，做一下编码转换就可以了。 虽然很容易得到期望的结果，但还是引出了我很深的疑惑，因为这表现出了跨平台的不一致性，而这正是我一直都在设法避免的。 环境说明 pg是在win10上的服务，安装方法见PostgreSQL zip 安装 go开发是在wsl2的debian上进行 对比pg是在虚拟机的debian上，安装方法见Debian上安装PostgreSQL 排查 统一服务端编码 \l命令查看，win上的pg：Encoding是utf8，Collate和Ctype是cp932，即gbk；debian上的pg全部是utf8。 win上使用bin\initdb -D data -U postgres -A password --locale=zh_CN.UTF-8 -E utf8 -W重新安装，全部变为utf8。 此时进行测试，发现数据库连接成功之后的错误消息都是utf8的，但连接过程中的错误消息还是gbk。 修改系统默认编码 修改windows的区域设置，勾选Beta版：使用 Unicode UTF-8 提供全球语言支持(U)，重启系统。 此时进行测试，无论哪个环节，错误消息都是utf8。但这很可能会影响到其它程序，尤其是比较旧的程序或为了兼容旧系统的程序，因此，虽然可行，但还是不能采用。 其它对比 使用Lazarus自带组件，配合libpq.dll，进行测试，在不修改系统默认编码的情况下，与go的程序表现一样。 结论 目前，暂时无法通过修改配置使pg达到在windows和debian上的表现完全一致，但通过配置可以达到除连接成功前的错误消息编码外的一致性（至少暂未发现其它不一致处）。

安装脚本 windows上安装PostgreSQL还是比较麻烦的，又是安装又是配置，还要设为系统服务，远不像linux上一条命令就搞定，所以参考网上其它资料整理后形成批处理文件，直接放在解压出来的pgsql根目录下执行即可。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 ::****************************************************************************** :: 注意：需要放到 PostgreSQL 根目录下执行！！ ::****************************************************************************** @echo off ::============================================================================== set pgpath=%cd% echo 1. 安装服务 echo 2. 卸载服务 set /p op="请选择操作: " if %op%==1 goto :Install if %op%==2 goto :Uninstall ::============================================================================== :Install echo **************************** echo * 注意：输入密码时不可见！ * echo **************************** ::初始化数据库簇 bin\initdb -D data -U postgres -A password -E utf8 -W ::是否开启局域网访问 set /p lan="是否开启局域网访问？[y/n]：" if %lan%==y ( echo host all all 0....

从其它平台迁移而来 用户 取消密码生命周期 1 ALTER profile default limit password_life_time unlimited; 修改用户密码 1 ALTER USER xxx IDENTIFIED BY xxx; 文件夹 查看文件夹 1 SELECT * FROM All_Directories; 表空间 查询表空间 1 SELECT * FROM Dba_Data_Files; 创建表空间 1 2 3 4 5 6 7 CREATE TABLESPACE 表空间名 DATAFILE '文件名' SIZE 初始大小 --单位k或m AUTOEXTEND ON --自动扩展 NEXT 扩展大小 MAXSIZE 最大大小 ; 表空间追加数据文件 1 2 3 4 5 6 7 ALTER TABLESPACE 表空间名 ADD DATAFILE '文件名' SIZE 初始大小 --单位k或m AUTOEXTEND ON --自动扩展 NEXT 扩展大小 MAXSIZE 最大大小 ; 删除表空间 1 2 DROP TABLESPACE 表空间名; --仅删除表空间 DROP TABLESPACE 表空间名 INCLUDING CONTENTS AND DATAFILES; --删除表空间及数据文件 导入数据 1 2 3 4 5 6 7 8 --原模式(用户名)导入 IMPDP 登录名/密码 DIRECTORY=泵导文件夹 DUMPFILE=泵导文件 LOGFILE=日志文件 SCHEMAS=模式名 --指定模式(用户名)导入 IMPDP 登录名/密码 DIRECTORY=泵导文件夹 DUMPFILE=泵导文件 LOGFILE=日志文件 SCHEMAS=原模式名:新模式名 --多模式(用户名)导入 IMPDP 登录名/密码 DIRECTORY=泵导文件夹 DUMPFILE=泵导文件 LOGFILE=日志文件 SCHEMAS=模式名1,模式名2 导出数据 1 EXPDP 登录名/密码@SID名 DIRECTORY=泵导文件夹 DUMPFILE=泵导文件 SCHEMAS=模式名

从其它平台迁移而来 坑 首次使用Oracle数据库，习惯性的用Navicat连起来，嗯？？？怎么报错连不上？ 数据库安装有问题？SQL Plus能连上啊！ 检查配置，没问题！ 重启，还是不行！ 重装Navicat，然并卵！ 解决 出现解决不了的问题，那就果断面向搜索引擎，看了好多文章，也试了个遍，甚至都开始更换dll了，最后发现其实还真和dll有关系。 这是默认的配置，可以看到，OCI用的是Navicat自带的oci.dll，换成Oracle安装路径的oci.dll，重启Navicat，完美！

从其它平台迁移而来 背景 客户使用的是南方XX的生产控制系统，需要把数据导入到我们自己的系统里，之前做过类似的东西，不难。 当看到南方XX使用的是Access数据库里，心中一沉……（此处省略1024G脏字）不管心中有多少头神兽奔腾而过，该做的事情终究还是要做的，谁让人家里客户是金主呢。 坑来了 代码写好了，测试也没问题（小BUG什么的分分钟搞定，不算数），通过！ 然后等着定时器，看第二回合是否通过。结果，卡卡卡的报主键重复！！！墨菲定律再次生效！ 查日志、打断点……一波操作后发现，是Access的时间过滤SQL执行结果和预期不一样！ 在Access里执行，正常！在程序里（ADO）执行，异常！！（此处再次省略1024G脏字） 开启面向搜索引擎编程模式，浪费一把又一把生命后（论浪费生命，某垃圾度绝对功不可没），终于找到了解决方案： 1 SELECT * FROM 表 WHERE 时间字段>CDate('字符串型的时间条件值') 吐槽 （此处直接省略1024T脏字） 微软早期的东西自己和自己都范冲，真是垃圾得不能再垃圾了，呃~不好意思，侮辱垃圾了！ 福利？ 分享个图吧，不知道算不算福利，反正我是差点笑岔气儿！

从其它平台迁移而来 今天在SQL Server的坑里跌得鼻青脸肿，折腾两三个小时，终于爬了出来，特此纪念一下，并以此明志！ 背景 传统行业的老旧ERP项目 数据库在公司服务器上，版本为SQL Server 2000，本地使用的数据库为SQL Server 2008 R2，数据库工具为Navicat 多数查询是从视图里查 部分业务是写在存储过程里 案发过程 业务功能扩充，需要在数据库中增加几个字段，于是直接从本地SQL Server 2008 R2连接公司SQL Server 2000，在表适当的位置插入所需字段，然后在Navicat中修改对应的视图和存储过程。 然后，奇怪的事情发生了，程序运行未报错也无错误日志，但就是出不来数据！ 打断点，跟踪调试，发现程序抛了个异常，但是没有任何代码能捕获到，而且也并未层层抛出，不知道中间哪个环节被吃了！先不管是哪吃了，把异常处理了应该就没问题了，毕竟之前都是正常的。 异常是类型不兼容和类型转换错误，好嘛，开始排查存储过程调用……一切正常！ 把查询的SQL输出，在Navicat中查询，也无结果！把查询条件去掉，结果出来了！排查条件吧，结果发现条件没问题！这TM就有点让人抓狂了！！！ 如此这般折磨几遍之后，结果丝毫没有任何改变！法了个克！ 还是先喝口水压压惊吧，然后！居然！！竟然！！！发现了不正常的东西！！！！一个布尔型的字段里居然出现了字符串！！！！！ 再次排查存储过程，存储过程正常！ 排查视图，视图不正常，但视图的SQL是正确的！这TM是个什么鬼情况？？？ 百思不得其解！ 再喝口水压压惊，把整个修改过程回忆一遍，灵光一闪，发现了点蛛丝马迹：修改视图时，只是在Navicat中确认了下视图的SQL是否正确，因为是正确的，所以并未进行修改！嗯，问题可能就是出在这里！！！ 那就在SQL Server 2008 R2里看一下吧，然后就发现了真相！我只想说：法了个克！！！ 重新修改视图，问题解决！ 真相 真相就是： 自作聪明的SQL Server其实愚蠢到令人发指！！！ 罢了，还是好好说话吧。 其实问题还是出在插入字段的这操作上，正常情况下可能是不会出什么问题的，但是当遇到视图里使用SELECT a.*, b.xx, c.xx ...这种方式时，问题就来了。 Navicat中看到的还是SELECT a.*, b.xx, c.xx ...这种方式，但是在SQL Server中却变成了SELECT a.f1, a.f2, a.f3, b.xx, c.xx ...这种方式，本来这也没什么大不了的，但是偏偏在对a表插入（并非追加）字段时就出问题了，因为SQL Server自己按字段顺序把原有的字段AS成了新字段，由于插入了字段，于是后面的字段就都错位了，结果自然就是驴唇不对马嘴了！ 思考 基于这次的发现，估计在SQL Server中调整字段的顺序也有可能会出现类似的情况，不过没有去测试验证，以后有时间了再说吧（不过估计不会再有时间了吧，就算有时间估计也不会去折腾讨厌的SQL Server吧）。 另外，服务器的数据库是SQL Server 2000，修改时用的是SQL Server 2008 R2，还不好就此说这是哪个版本出现的问题，也许新版本已经解决过了吧，这还是留给有心的朋友去研究吧。 最后，我只想说，MySQL它不香么？PostgreSQL它不香么？为什么要把时间浪费在SQL Server上？

从其它平台迁移而来 越来越喜欢绿色免安装的产品了，MySQL也不例外，由其是MySQL 8，于是就重点研究了下如何安装zip版的MySQL。 安装脚本 以下是参考网上其它资料整理后的批处理文件，直接放在MySQL解压出来的根目录下执行即可。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 @echo off ::============================================================================== set mysqlPath=%cd% echo 1....