小白学习mysql之函数
## 导语 曾经我以为,学会了select、update、insert和delete之后,我就学会了数据库~,要不是到公司看到SQL里充满了密密麻麻的的各种函数,我差点就信了~,当初的自己是多么的天真,o(╯□╰)o 现在的数据库功能之强大,使得很多的业务逻辑转移到了数据库上,更多的事务和存储过程出现在系统中,因此,拼接字符串成为了当代程序员必备的技能,要想练就拼接千百行的SQL,语句,你不能不熟练掌握以下函数. ### CONCAT() 可谓是,拼串圈函数的一哥,有了他,便可以实现两个乃至多个语句的拼接。 ``` mysql> select concat('ma yun', ' is cloud lao wang','!'); +--------------------------------------------+ | concat('ma yun', ' is cloud lao wang','!') | +--------------------------------------------+ | ma yun is cloud lao wang! | +--------------------------------------------+ ```
特别注意,每个语句的连接处是否需要空格,而且如果里面的每个语句都为空的话,结果为null。
### CONCAT_WS() 和一楼是兄弟,使用方式CONCAT_WS(separator,str1,str2,...),其中第一个为分隔符,为后面的每两个字符串之间自动添加该分隔符。有很多时候拼写的sql语句条件非常多,就常用该函数,比如where后面要加5个 and 条件句,这个时候它便显身手了。 ``` mysql> select concat_ws(' and ','c=5',IF(1>2,'d=3',null),'b=2'); +---------------------------------------------------+ | concat_ws(' and ','c=5',IF(1>2,'d=3',null),'b=2') | +---------------------------------------------------+ | c=5 and b=2 | +---------------------------------------------------+ ```
技巧&注意点:第一个分隔符若为and之类的关键字,注意俩边要有空格。注意这个函数不能跳过空的字符串,只能跳过null,所以如果里面的字符串项中若有判断语句,一定不要把字符串赋值为空。
### ELT *ELT(N,str1,str2,str3,...)* 本函数是field函数的补充函数,根据N的值选择第N个字符串,比如性别在数据表中一般存为数字,如1代表男,2代表女,当我们需要根据性别代码显示性别的时候就可以使用ELT函数。 ``` mysql> select elt(1,'男','女'); +------------------+ | elt(1,'男','女') | +------------------+ | 男 | +------------------+ ``` 本函数也可以用作sql语句的扩展,比如可以根据传入的参数进行选择排序方式等,这样就大大增强了一个sql语句的处理能力,类似程序设计语言中的case或者if语句等。
### IF *IF(expr1,expr2,expr3)* IF函数大概很多人会觉得太过于简单,实时确实如此,然而越是简单的东西越容易忽视一些细节而犯错,前段时间就发现了一个用IF所导致的bug。大致情况如下: ``` mysql> select if('hello','1','2'); +---------------------+ | if('hello','1','2') | +---------------------+ | 2 | +---------------------+ ``` 当然事实上第一个参数大部分请情况是一个变量(假设为V_STR),很多时候写着写着便可能会用到上面的语句来判断V_STR是否为空,这完全是来自对简单问题的忽略以及有可能和IFNULL傻傻分不清楚。同时这里的确和编程语言中的IF ELSE是不一样的,上面的情况如果出现在编程语言中,则会返回'1';
### FIND_IN_SET
原型 FIND_IN_SET(str,strlist) 在数据库和数据文件进行交互时,往往存在编码和实际数据的转化,例如现在需要向一张学生表中导入一批学生数据,在性别这一列,excel中显示的为“男,女”,而数据库中存储的则是’1,2‘,现在需要把实际数据转化为编码数据就可以使用该函数。 ``` mysql> select find_in_set('男','男,女'); +---------------------------+ | find_in_set('男','男,女') | +---------------------------+ | 1 | +---------------------------+ ``` 函数返回,第一个参数在第二个参数中出现的下标位置(从1开始)。如果不存在返回0; 当然例子举得比较简单,实际运用可能用于字符串中有很多种类别时,比如一个公司的职位等。另外,strlist是以','号隔开的,所以慎重考虑第一个参数中包含有','号,并检查函数能凑效。
### LOCATE LOCATE,顾名思义是一个定位的函数,该函数返回第一个参数在第二个参数中的位置,比较简单。但在数据库中,LOCATE似乎承担了更多的责任,更多的时候,它总在’试探‘某些条件是否成立,根据试探结果进行sql语句的分类拼接。 ``` mysql> select if(locate(',','1,2,3'),'你们好','你好'); +-----------------------------------------+ | if(locate(',','1,2,3'),'你们好','你好') | +-----------------------------------------+ | 你们好 | +-----------------------------------------+ ``` 上面的例子中假设’1,2,3‘是一个学生id,所组成的字符串,那么根据locate就能判断是否是一个学生还是多个学生,同时locate还能根据where条件句后是否包含某个关联字表的字段进行选择性关联该表。locate给sql语句的拼接带来了很多的便捷性。
### QUOTE 最后一个但绝对是最重要的一个,有关安全带问题永远都是最重要的,当程序中有用户输入数据的话,一定要用本函数来进行处理,防止基本的sql注入问题。 ``` mysql> select concat('where id = ','5411 or 1=1'),concat('where id = ',quote('5411 or 1=1')); +-------------------------------------+--------------------------------------------+ | concat('where id = ','5411 or 1=1') | concat('where id = ',quote('5411 or 1=1')) | +-------------------------------------+--------------------------------------------+ | where id = 5411 or 1=1 | where id = '5411 or 1=1' | +-------------------------------------+--------------------------------------------+ ``` 上例那最基本的sql注入作为演示,如果在一个软件的用户界面是输入学生的ID,这样用户输入’5411 or 1=1‘的时候,全部学生的信息就出来啦!这时如果经过quote函数进行处理,那么就会什么也搜不到,保证了数据的安全,同时quote也可以正确的处理转义问题。
## 总结 mysql数据库提供的这些功能强大的函数,虽然没有编程语言函数那般强大,但只要动动脑筋,还是基本上可以满足几乎所有你想实现的。而且,讲这些函数和基本的业务处理结合起来,就会发现每一个函数的强大。