mysql查询条件-不区分大小写

项目中遇到一个bug,查了一下问题在于mysql查询条件不区分大小写

比如 test表里面存储了两列数据

col0 col1 col2
tmp 1 1
TMP 2  2

查询语句 select * from test where col0 = 'tmp'

两行都能搜索出来。第一次拿到这一个结果,我的内心是这样的:WTF、EXM、你TMD是来逗我的吧

但事实摆在面前,不能不认怂。下一步究其根本,这个答案很靠谱

以下引自:https://blog.csdn.net/Veir_123/article/details/73730751

Mysql默认的字符检索策略:utf8_general_ci,表示不区分大小写;utf8_general_cs表示区分大小写,utf8_bin表示二进制比较,同样也区分大小写 。(注意:在Mysql5.6.10版本中,不支持utf8_genral_cs!!!!)

创建表时,直接设置表的collate属性为utf8_general_cs或者utf8_bin;如果已经创建表,则直接修改字段的Collation属性为utf8_general_cs或者utf8_bin。

直接修改sql语句,在要查询的字段前面加上binary关键字即可。
-- 在每一个条件前加上binary关键字
select * from test where binary col0 = 'tmp';

-- 将参数以binary('')包围
select * from test where col0 = binary('tmp');

sql-按条件统计非重复值,count(distinct case when)使用

背景

项目中,遇到一个统计需求,从某张表中按照条件分别统计。刚开始想到用union all的写法,太臃肿,后来使用count(distinct case when)解决此问题

count

数据统计中,count出现最频繁

最简单的用法

select count(*) from table where ....

select count(distinct xx) from table where ...

但最简单的用法也有其深刻的地方,比如这里其实有3种写法,count(1)、count(*)、count(字段),它们有什么区别呢?

  • count(1) 和 count(*)

count(1)和count(*)差别不大,使用count(*)时,sql会帮你自动优化,指定到最快的字段。所以推荐使用count(*)

  • count(*) 和 count(字段)

count(*)会统计所有行数,count(字段)不会统计null值

count(case when)

条件统计,即对某个table分条件统计,比如表test_db,有一个字段user_id(可能重复), gender(man、women),需要统计man和women的人数

可以使用where分别统计

select count(distinct user_id) as man_cnt from test_db where gender = 'man'

select count(distinct user_id) as women_cnt from test_db where gender = 'women'

也可以使用按条件统计

select count(distinct case gender = 'man' then user_id end) as man_cnt

, count(distinct case gender = 'women' then user_id end) as woman_cnt

from test_db

 

sql中!=与的区别

背景

sql中表示不等于关系时,有两种写法!=、<>,抱着刨根问底的精神,看看这两个符号到底有什么不同

标准答案

https://stackoverflow.com/questions/18015422/what-is-difference-between-and-in-sql-server

搬运过来

这两个符号没有任何区别,你使用哪个都一样。

Microsoft的文档中,这两个符号的意义相同,都是 Not Equal To

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/not-equal-to-transact-sql-exclamation?view=sql-server-2017

但,<>是ANSI 99 SQL标准中的定义,!=不是。所以,不是所有的DB引擎都支持!=。

最终推荐使用<>。

mysql-使用联合索引

背景

项目使用mysql数据库,某张表数据量2000W左右,比较大,在组合查询时发生超时,需要优化。

优化

查询优化常用方法就是建立索引

查询语句样例:查询在20181001-20181010时间范围内A=a且B=b的数据

select * from table where A = 'a' and B = 'b' and Date > '20181001' and Date < '20181010'

按照查询建立了一个组合索引 idx_query(A, B, Date),查询仍然报超时。然后开始研究索引原理,最后更改索引中列的顺序为idx_query(B,A,Date)解决。

---------------------------------------------------------------------------------------------

上面只是简单的项目背景,下面从原理细细解读索引

索引原理

首先,出问题以后再建立索引,是一种不好的习惯。排查、定位、解决问题会耗费很多开发时间和精力。

当然,不充分思考就给大多数列建立单列索引,给所有查询建立联合索引,是一种更坏的习惯。毕竟删除索引风险远高于添加索引。

使用索引需要成本:

  • 空间成本:索引是占空间的,大表的联合索引占用空间不可忽略
  • 时间成本:使用不对的索引会浪费时间
  • 更新成本:增删改操作只要跟索引有关系,就需要更新索引

索引适合在返回大表中很小一部分数据时使用,很小当然越小越好,5%以下还可以,1%以下更好,如果表的数据量非常大,千万级别,返回行数要小到0.1%以下

从很大表中用联合条件查询出很小的数据,等价于count(distinct 联合条件) 的值很大,这个值被称为Cardinality,索引适合度=Cardinality/表记录数。

索引误区

多个单列索引(如idx_A, idx_B)不等同于联合索引(idx_A_B)

两个联合索引里的列顺序不同,这两个联合索引也不等价,适用场景不同

联合索引idx_A_B能够覆盖的场景

  • A = ? and B = ?
  • A = ? and B in (?, ?, ?)
  • A = ?
  • A in (?,?,?)

覆盖场景可以根据索引存储结构(B+Tree)推导出。

上面罗列的几种覆盖场景,效率由上至下越来越低,因为扫描行数越来越大

联合条件中,最左侧的索引列很重要,最好使用Cardinality最大的列。

总结

项目中遇到的问题,最终根据索引原理中 最左侧索引列使用Cardinality最大列 解决。

苹果Magic Mouse修复

苹果Magic Mouse已经损坏了好长时间了,故障现象就是鼠标无法移动,但是点击,触摸都是有效的。网上搜索了一下,貌似这种故障很多。

大概率是Apple设计原因导致的故障。原因在于这个芯片跟罗技的鼠标用的同一个型号的,而罗技的鼠标很少发生类似的故障。初步猜测是Apple设计的时候,地线是跟外壳联通的,导致静电会沿着金属外壳击穿芯片内部管脚。

继续阅读苹果Magic Mouse修复

使用java获取当前是星期几

有个小需求,获取今天是周几,想我大java,获取一个星期几不是手到擒来。

第一想法,System.currentTimeMillis(),这是个时间戳,还得处理秒数,太麻烦。

第二想法,new Date() deprecated?  好吧。

第三想法,Calendar,简单写下代码,开始调试

weekDay = 3,wtf?今天是周二,怎么给了我一个3,只听说过从0、1计数,没听说过从2开始计数的啊!

继续查

国际上是以星期日为一周第一天的开始,Calendar中提供的DAY_OF_WEEK获取的一周也是以星期日作为一周的开始。

而中国的习惯是以星期一作为一周的开始,所以需要根据项目的实际需求来设置。

好吧,真是人生处处皆学问,写代码还能学知识。

怎么办呢?Calendar有个api叫setFirstDayOfWeek,原来从哪一天开始是可以设置的,java也是蛮人性化的么~

继续调试

weekDay = 3

这就有点意思了,这个api是用来看的么? 继续查

官方文档一大堆,概括为:

setFirstDayOfWeek的方法意思只对WEEK_OF_MONTH WEEK_OF_YEAR 有作用.

WEEK_OF_MONTH :当前Calendar日期对象是当前月的第几周.

WEEK_OF_YEAR :当前Calendar 日期对象是当前年的第几周.

好吧,老老实实写下比较丑陋的代码

最低成本解决招商银行香港网银账户长时间不交易被冻结的问题

最近收到招商银行香港银行的短信:

查询了一下,根据香港的规定,"账户连续两年内没有任何主动交易,则账户自动转为冻结状态,成为“睡眠户”,该类账户不允许进行出款交易。若您需要重新恢复该账户的使用,需要您填妥解除睡眠户表格寄送给香港分行审批,审批通过后,香港分行会主动联系您,核实后可以解除。"

以前总是傻傻的进行一次国内的转账操作,每次都被收高昂的手续费。今天终于想明白了,只要进行交易都算,那么干脆做一笔最小的活期转定期的存款,比如100块美金的一周定期,就搞定了。

PLMN概念和应用设置

PLMN

PLMN(Public Land Mobile Network,公共陆地移动网络)

该网路必须与公众交换电话网(PSTN)互连,形成整个地区或国家规模的通信网。

PLMN = MCC + MNC

中国移动的PLMN为46000,46002,46007

中国联通的PLMN为46001,46006

中国电信的PLMN 46003,46005

 

PLMN的分类 

对于一个特定的终端来说,通常需要维护几种不同类型的PLMN列表,每个列表中会有多个PLMN。

  RPLMN(Registered PLMN 已登记PLMN):

已登记PLMN。是终端在上次关机或脱网前登记上的PLMN。

在3GPP 2003年第TSG TP-21次会议上决定,将该参数从USIM卡上删掉,而将其保存在终端的内存中。

  EPLMN(Equivalent PLMN 等效PLMN):

为与终端当前所选择的PLMN处于同等地位的PLMN,其优先级相同。

  EHPLMN(EquivalentHome PLMN等效本地PLMN):

为与终端当前所选择的PLMN处于同等地位的本地PLMN。

其实:EHPLMN和EPLMN就好比是中移动的新建的158网络,

而EPLMN就好比是原来的135~139网络。

  HPLMN(Home PLMN 归属PLMN):

为终端用户归属的PLMN。也就是说,终端USIM卡上的IMSI号中包含的MCC和MNC与HPLMN上的MCC和MNC是一致的,

对于某一用户来说,其归属的PLMN只有一个。

  VPLMN(Visited PLMN 访问PLMN):

为终端用户访问的PLMN。其PLMN和存在SIM卡中的IMSI的MCC,MNC是不完全相同的。当移动终端丢失覆盖后,一个VPLMN将被选择。

  UPLMN(User Controlled PLMN 用户控制PLMN):

是储存在USIM卡上的一个与PLMN选择有关的参数。

  OPLMN(Operator Controlled PLMN 运营商控制PLMN):

是储存在USIM 卡上的一个与PLMN选择有关的参数。

  FPLMN(Forbidden PLMN禁用PLMN):

为被禁止访问的PLMN,通常终端在尝试接入某个PLMN被拒绝以后,会将其加到本列表中。

  APLMN(Approve PLMN 可捕获PLMN):

为终端能在其上找到至少一个小区,并能读出其PLMN标识信息的PLMN。

PLMN的选择优先级:

不同类型的PLMN其优先级别不同,终端在进行PLMN选择时将按照以下顺序依次进行:

RPLMN

EPLMN

HPLMN

EHPLMN

UPLMN

OPLMN

其他的PLMN

From:      http://baike.baidu.cn/view/544151.htm

 

3GPP 23122协议规定的小区重选PLMN选择顺序

3GPP 23122协议规定的小区重选PLMN选择顺序

At switch on or recovery from lack of coverarge,用户首先尝试选择的是RPLMN(registered PLMN),选择失败后按照如下顺序选择:

1)         HPLMN(如果EHPLMN列表不存在或为空)或最高优先级的EHPLMN(如果EHPLMN列表存在);

2)         在SIM卡数据中按照UPLMN优先级顺序选择;(User Controlled PLMN Selector with Access Technology)

3)         在SIM卡数据中按照OPLMN优先级顺序选择;(Operator Controlled PLMN Selector with Access Technology)

4)         随机选择RSCP值大于-84dBm(足够强度信号质量)形成的PLMN接入技术组合;

5)         当信号强度不够时,根据接收信号强度递减排序选择其他所有PLMN。

From:      http://blog.sina.com.cn/s/blog_6617106b01013nxi.html

 

手机PLMN相关处理

因为有多个PLMN,USIM卡中存储的的EHPLMN List用支持多个HPLMN lists;

对于2G SIM卡中是不存在EHPLMN List存储区域。

手机支持从UICC卡文件系统中读取EHPLMN List,只有在UICC文件系统支持EHPLMN List的情况下。

而EHPLMN List就是决定手机支持的PLMN;对SIM卡没有EHPLMN List,而只有HPLMN即IMSI一个;会将HPLMN作为终端的PLMN.

当SIM卡的PLMN事46002,而手机注册的基站网络PLMN是46000;将会是一种漫游状态;而实际上不是。

 

鉴于这个问题,手机方案厂商采取一些措施解决:

高通将EHPLMN List可存储在手机内存上;2G卡将从手机内存中读取EHPLMN List,USIM或从卡中读取;

需要设置NV:

NV65602:Location – /nv/item_files/modem/nas/ehplmn

Default value – 0x03 0x64 0xf0 0x00 0x64 0xf0 0x20 0x64 0xf0 0x70 (46000, 46002, 46007)

 

NV70189:Location – /nv/item_files/modem/nas/ tdscdma_op_plmn_list

Default value – 0x03 0x64 0xf0 0x00 0x64 0xf0 0x20 0x64 0xf0 0x70 (46000, 46002, 46007)

Modem代码中处理与PLMN相关的函数:

reg_sim.c :reg_sim_read_ehplmn_list

参考链接


PLMN概念和应用设置

ubuntu 16.04防止SSH暴力登录攻击

最近观察服务器的认证日志,发现有些国外的IP地址,多次尝试破解服务器的密码进行登录。于是希望能将多次尝试SSH登录失败的IP阻止掉。

查看日志文件:

看到很多如下的日志:

Failed password for root from 123.15.36.218 port 51252 ssh2
reverse mapping checking getaddrinfo for pc0.zz.ha.cn [218.28.79.228] failed – POSSIBLE BREAK-IN ATTEMPT!
Invalid user akkermans from 218.28.79.228
pam_unix(sshd:auth): check pass; user unknown
pam_unix(sshd:auth): authentication failure; logname= uid=0 euid=0 tty=ssh ruser= rhost=218.28.79.228

来统计一下有多少人在暴力破解root密码

如果已经禁用了root登录,则看一下暴力猜用户名的统计信息

某个人尝试了129次。为了防范于未然,我们可以做些配置,让服务器更加安全。

下面的三个方法,可以完全使用,也可以部分使用。一般建议使用其中的第一条跟第三条。

1. 修改SSH端口,禁止root登陆

修改/etc/ssh/sshd_config文件

2. 禁用密码登陆,使用RSA私钥登录

如果服务器只允许使用私钥登录的,但是如果想在别的电脑上临时SSH上来,又没带私钥文件的情况下,就很麻烦。所以还是保留密码验证登录。不管怎样,这一条还是先列出来

3. 安装denyhosts

denyhostsPython语言写的一个程序,它会分析sshd的日志文件,当发现重复的失败登录时就会记录IP/etc/hosts.deny文件,从而达到自动屏IP的功能。现今denyhosts在各个发行版软件仓库里都有。

注意在ubuntu 16.04系统上,如果通过远程的SSH登录到服务器上执行安装命令的话,会由于默认情况下RESET_ON_SUCCESS = yes #如果一个ip登陆成功后,失败的登陆计数是否重置为0这部分,默认情况下是关闭的。而如果恰好我们又出现自己输入的错误密码错误累计次数超过5次的情况(即使后面有成功登录的记录也不行),会导致我们自己当前登录的地址也被阻止的情况。这种情况发生之后,会导致我们自己无法控制服务器(这个阻塞是在iptables层阻塞的,如果要恢复,在iptables中删除已经添加的记录才可以)。解决办法就是换一个新的IP地址登录服务器,然后修改RESET_ON_SUCCESS这个参数,并重启denyhosts服务。如果是阿里云或者腾讯云的服务器,可以尝试从他们网站上提供的网页版本的Shell进行操作。

对于ubuntu 16.04系统,建议使用如下方式进行安装:

默认配置就能很好的工作,如要个性化设置可以修改/etc/denyhosts.conf

查看/etc/hosts.deny发现里面已经有3条记录。

目前ubuntu 16.04系统源里的denyhosts存在一个BUG,就是系统重启之后,iptables中的拦截设置没有恢复。具体的讨论以及描述,参考Iptables not persistent,代码应该已经增加了,目前还没合并到主分支。

参考链接