mysql 字符串常用处理

(手册型内容)参考链接:

https://www.cnblogs.com/geaozhang/p/6739303.html

MYSQL一些字符串的处理，如拼接，截取等，便于用在同一字段中多个值的处理(常用的)

下面转载至 http://blog.51cto.com/intheway/1113513

1.  -- 返回time有多少秒 不要忘记单引号'' 
2.  
3. SELECT  TIME_TO_SEC( '22:23:00' ) 
4. -- 返回一个Unix时间戳(从'1970-01-01 00:00:00′GMT开始的秒数,date默认值为当前时间)  
5. select  UNIX_TIMESTAMP();  
6. -> 882226357      -- 1357792552 
7. select  UNIX_TIMESTAMP( '1997-10-04 22:23:00' );  
8. -- 875974980 
9. -- FROM_UNIXTIME(unix_timestamp)  
10. -- 以'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式返回时间戳的值(根据返回值所处上下文是字符串或数字)  
11. select  FROM_UNIXTIME(875996580); 
12. -- 1997-10-05 04:23:00 

 

 

1. -------------------各种mysql字符串截取与拼接（便于一个字段中多个值以符号隔开的取值）--- 
2.  
3. SUBSTRING (str,pos); 
4. SELECT SUBSTRING (SUBSTRING_INDEX( 'BGP-Beijing-59.151.105.130-AD_read-YeZongKun' , '-' ,3),   
5. (LENGTH(SUBSTRING_INDEX( 'BGP-Beijing-59.151.105.130-AD_read-YeZongKun' , '-' ,2))+2)); 
6.  
7. SUBSTRING (str,pos,len); 
8. SUBSTRING (str  FROM  pos  FOR  len); 
9. SUBSTRING (str,pos); 
10. SUBSTRING (str  FROM  pos); 
11. -- ratically 
12. SELECT SUBSTRING ( 'Quadratically' ,5);  
13. -- index 第四个字符截取tbarbar 
14. SELECT SUBSTRING ( 'footbarbar' FROM  4);   
15. -- 从下标5处截取6个长度的字符串 
16. SELECT SUBSTRING ( 'Quadratically' ,5,6);  
17. -- 倒数截取3个字符 
18. SELECT SUBSTRING ( 'Sakila' ,-3);  
19. SELECT SUBSTRING ( 'Sakila' ,-1);  
20. -- aki 倒数截取5个字符中的前3个 
21. SELECT SUBSTRING ( 'Sakila' ,-5,3);  
22. -- ki  
23. SELECT SUBSTRING ( 'Sakila' FROM  -4  FOR  2);   

subString_index与subString,concat结合使用

 

 

1. -- 返回字符串 str 中在第 count 个出现的分隔符 delim 之前的子串 
2. SUBSTRING_INDEX(str,delim, count ); 
3. -- www.mysql 正数，返回从最后的(从左边开始计数)分隔符到左边所有字符 
4. SELECT  SUBSTRING_INDEX( 'www.mysql.com.cn' , '.' ,2); 
5. -- mysql.com 负数，返回从最后的(从右边开始计数)分隔符到右边所有字符 
6. SELECT  SUBSTRING_INDEX( 'www.mysql.com' , '.' ,-2); 
7.  
8. -- 字符串替换 
9. -- replace(field,str,str1)将表里面的 field字段的str替换为str1  
10. UPDATE table SET name = REPLACE ( name ,  'abc' ,  '123' );    
11. -- 字符串拼接CONCAT(str1,str2,...) 
12. UPDATE TABLE SET name =CONCAT( name , '-old' );    
13.  
14. -- concat 与 SUBSTRING_INDEX(str,delim,count)共同使用拼接字符串 
15. -- update tb_user set name = concat(SUBSTRING_INDEX(name,'-',2),'-','1234') where uid = 271338 ;  
16.  
17. -- ELT(N,str1,str2,str3,…)返回第N个字符串（N小于1或大于参数个数返回NULL） 
18. SELECT  ELT(2,1035089,319,6286,1997,1899); 
19. -- 返回319 
20.  
21. -- 查询表的结构 
22. desc  user_info 
23. -- case when then 
24. select date , min ( case when  type= 'a' then  value  end ), min ( case when  type= 'b' then  value  end )       
25. from  tb 
26. group by date   

 

eg:

SELECT SUBSTRING(SUBSTRING_INDEX('BGP-Beijing-59.151.105.130-AD_read-YeZongKun','-',3),

(LENGTH(SUBSTRING_INDEX('BGP-Beijing-59.151.105.130-AD_read-YeZongKun','-',2))+2));

 

 

1. -- INSTR(str,substr) 返回字符串substr在字符串str第一次出现的位置(str不包含substr时返回0)  
2. select  INSTR( 'foobarbar' ,  'bar' );  -- 4 
3. -- 返回删除了左空格的字符串str mysql> select LTRIM(‟ barbar‟); -> „barbar‟ 
4. SELECT  LTRIM(str) 
5. -- REVERSE(str) 颠倒字符串str的字符顺序并返回 mysql> select REVERSE(‟abc‟); -> „cba‟ 
6. SELECT  REVERSE(str) 
7.  
8. -- INSERT(str,pos,len,newstr) 把字符串str由位置pos起len个字符长的子串替换为字符串newstr并返回  
9. select INSERT ( 'Quadratic' , 3, 4,  'What' );  
10. -- QuWhattic 

 

使用：rankingListInfo(500,300,200,123,409,) 

1. SELECT INSERT (( SELECT  rankingListInfo  FROM  user_info  WHERE  uid = 1),17,7,( SELECT  SUBSTRING_INDEX(rankingListInfo, ',' ,2)  FROM  user_info  WHERE  uid =1))   
2.  
3. SELECT RIGHT (( SELECT INSERT (( SELECT  rankingListInfo  FROM  user_info  WHERE  uid = 1),17,7,( SELECT  SUBSTRING_INDEX(rankingListInfo, ',' ,2)  FROM  user_info  WHERE  uid =1))    
4. ),13)
5.  
6.  -----PHP中分别读取---
7. list($one,$two,$three,$four,$five) = explode('-',$rankingListInfo);
8. 
   
9. 这样就把字符串分割为5份，分别赋值给one，two，three...

 

--  给变量赋值----进而进行其他赋值操作同一张表中即查询又update  ----------------------

 

1. DECLARE  @list  VARCHAR ; 
2. SET  @list = ( SELECT  SUBSTRING_INDEX(( SELECT  rankingListInfo  FROM  user_info  WHERE  uid = 1), ',' ,-4)); 
3.  
4. SELECT  @list; 
5.  
6. UPDATE  user_info  set  rankingListInfo = @list  WHERE  uid =1; 
7.  
8. SELECT  rankingListInfo  from  user_info  WHERE  uid= 1; 
9. -- 拼接字符串-------------------- 
10. UPDATE  user_info  SET  rankingListInfo = CONCAT(rankingListInfo, '500,500,' )  WHERE  uid =1;