MYSQLのいくつかの文字列の処理、例えばつなぎ、切り取りなど、同じフィールドの複数の値の処理に便利です.
15303 ワード
- -- time ''
-
- SELECT TIME_TO_SEC('22:23:00')
- -- Unix ( ’1970-01-01 00:00:00′GMT ,date )
- select UNIX_TIMESTAMP();
- -> 882226357 -- 1357792552
- select UNIX_TIMESTAMP('1997-10-04 22:23:00');
- -- 875974980
- -- FROM_UNIXTIME(unix_timestamp)
- -- ’YYYY-MM-DD HH:MM:SS’ YYYYMMDDHHMMSS ( )
- select FROM_UNIXTIME(875996580);
- -- 1997-10-05 04:23:00
- ------------------- mysql ( )---
-
- SUBSTRING(str,pos);
- 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));
-
- SUBSTRING(str,pos,len);
- SUBSTRING(str FROM pos FOR len);
- SUBSTRING(str,pos);
- SUBSTRING(str FROM pos);
- -- ratically
- SELECT SUBSTRING('Quadratically',5);
- -- index tbarbar
- SELECT SUBSTRING('footbarbar' FROM 4);
- -- 5 6
- SELECT SUBSTRING('Quadratically',5,6);
- -- 3
- SELECT SUBSTRING('Sakila',-3);
- SELECT SUBSTRING('Sakila',-1);
- -- aki 5 3
- SELECT SUBSTRING('Sakila',-5,3);
- -- ki
- SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
subString_indexとsubString,concatを組み合わせて使用
- -- str count delim
- SUBSTRING_INDEX(str,delim,count);
- -- www.mysql , ( )
- SELECT SUBSTRING_INDEX('www.mysql.com.cn','.',2);
- -- mysql.com , ( )
- SELECT SUBSTRING_INDEX('www.mysql.com','.',-2);
-
- --
- -- replace(field,str,str1) field str str1
- UPDATE table SET name=REPLACE(name, 'abc', '123');
- -- CONCAT(str1,str2,...)
- UPDATE TABLE SET name=CONCAT(name,'-old');
-
- -- concat SUBSTRING_INDEX(str,delim,count)
- -- update tb_user set name = concat(SUBSTRING_INDEX(name,'-',2),'-','1234') where uid = 271338 ;
-
- -- ELT(N,str1,str2,str3,…) N (N 1 NULL)
- SELECT ELT(2,1035089,319,6286,1997,1899);
- -- 319
-
- --
- desc user_info
- -- case when then
- select date,min(case when type='a' then value end),min(case when type='b' then value end)
- from tb
- 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));
- -- INSTR(str,substr) substr str (str substr 0)
- select INSTR('foobarbar', 'bar'); -- 4
- -- str mysql> select LTRIM(‟ barbar‟); -> „barbar‟
- SELECT LTRIM(str)
- -- REVERSE(str) str mysql> select REVERSE(‟abc‟); -> „cba‟
- SELECT REVERSE(str)
-
- -- INSERT(str,pos,len,newstr) str pos len newstr
- select INSERT('Quadratic', 3, 4, 'What');
- -- QuWhattic
:rankingListInfo(500,300,200,123,409,)
- SELECT INSERT((SELECT rankingListInfo FROM user_info WHERE uid = 1),17,7,(SELECT SUBSTRING_INDEX(rankingListInfo,',',2) FROM user_info WHERE uid =1))
-
- 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))
- ),13)
-
- -----PHP ---
- list($one,$two,$three,$four,$five) = explode('-',$rankingListInfo);
- 5 , one,two,three...
--
変数に値を割り当てる----さらに他の値を割り当てる操作を同じテーブルでクエリーしてupdate-----
- DECLARE @list VARCHAR;
- SET @list = (SELECT SUBSTRING_INDEX((SELECT rankingListInfo FROM user_info WHERE uid = 1),',',-4));
-
- SELECT @list;
-
- UPDATE user_info set rankingListInfo = @list WHERE uid =1;
-
- SELECT rankingListInfo from user_info WHERE uid= 1;
- -- --------------------
- UPDATE user_info SET rankingListInfo = CONCAT(rankingListInfo,'500,500,') WHERE uid =1;