mysql常用字符串操作函數(shù)大全,以及實(shí)例

1、mysql常用字符串操作函數(shù)大全，以及實(shí)例 今天在論壇中看到一個(gè)關(guān)于mysql的問(wèn)題，問(wèn)題如下good_id cat_id12654 665,56912655 601,4722goods_id是商品idcat_id是分類id當(dāng)我，怎么根據(jù)這種分類ID查數(shù)據(jù)（一個(gè)商品有多個(gè)分類，而且用逗號(hào)隔開(kāi)了）我現(xiàn)在用的是like 這樣的話，輸入一個(gè)分類id是688，或者4722都能出來(lái)這個(gè)商品，但輸入一個(gè)722也出來(lái)這個(gè)商品了。如果用like做的話，肯定會(huì)有問(wèn)題的，我的開(kāi)始的想法是，把cat_id里面的字符串換成數(shù)組，這樣可以利用mysql里面的in操作，這樣就不會(huì)出現(xiàn)查找722，而4722類別下的產(chǎn)品都跑

2、出來(lái)了。我從網(wǎng)上找了半天，這方面的字符串操作函數(shù)，沒(méi)找到，不過(guò)我發(fā)現(xiàn)了find_in_set這個(gè)函數(shù)雖然不能，將字符串轉(zhuǎn)換成數(shù)組，但是也不會(huì)出現(xiàn)上面的情況。我發(fā)現(xiàn)自己有好多函數(shù)不知道，所以我從手冊(cè)中,以及網(wǎng)上收集了半天，做了一些例子。一，測(cè)試準(zhǔn)備查看復(fù)制打印?1. 測(cè)試表 2. CREATETABLEstring_test( 3. idint(11)NOTNULLauto_incrementCOMMENT用戶ID, 4. namevarchar(50)NOTNULLdefaultCOMMENT名稱, 5. jobvarchar(23)NOTNULLCOMMENT工作, 6. sextinyin

3、t(1)NOTNULLdefault1COMMENT性別, 7. hobbyvarchar(100)charactersetutf8collateutf8_unicode_cidefaultNULLCOMMENT愛(ài)好, 8. PRIMARYKEY(id) 9. )ENGINE=MyISAMDEFAULTCHARSET=utf8AUTO_INCREMENT=1; 10. 11. 測(cè)試數(shù)據(jù) 12. INSERTINTOstring_test(id,name,job,sex,hobby)VALUES 13. (1,tank,農(nóng)民工,1,軍棋,游戲,fishing), 14. (2,zhang,DUC

4、K,0,fly,makefirend), 15. (3,ying,nojob,1,flying,driving,testing), 16. (4,tankzhang,loveyourlove,1,iloveyou);測(cè)試表CREATE TABLE string_test ( id int(11) NOT NULL auto_increment COMMENT 用戶ID, name varchar(50) NOT NULL default COMMENT 名稱, job varchar(23) NOT NULL COMMENT 工作, sex tinyint(1) NOT NULL defaul

5、t 1 COMMENT 性別, hobby varchar(100) character set utf8 collate utf8_unicode_ci default NULL COMMENT 愛(ài)好, PRIMARY KEY (id) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;測(cè)試數(shù)據(jù)INSERT INTO string_test (id, name, job, sex, hobby) VALUES(1, tank, 農(nóng)民工, 1, 軍棋,游戲,fishing),(2, zhang, DUCK, 0, fly,make fir

6、end),(3, ying, no job, 1, flying,driving,testing),(4, tankzhang, love your love, 1, i love you);idnamejobsexhobby1tank農(nóng)民工1軍棋,游戲,fishing2zhangDUCK0fly,make firend3yingno job1flying,driving,testing4tankzhanglove your love1i love you二，mysql字符串操作函數(shù)1，UPPER和UCASE返回字符串str，根據(jù)當(dāng)前字符集映射(缺省是ISO-8859-1 Latin1)把所有

7、的字符改變成大寫。該函數(shù)對(duì)多字節(jié)是可靠的。mysql select name,UPPER(name) from string_test where name=tank;+-+-+| name | UPPER(name) |+-+-+| tank | TANK |+-+-+1 row in set (0.00 sec)2，LOWER和LCASE返回字符串str，根據(jù)當(dāng)前字符集映射(缺省是ISO-8859-1 Latin1)把所有的字符改變成小寫。該函數(shù)對(duì)多字節(jié)是可靠的。mysql select sex,LCASE(job) from string_test where job=DUCK;+-+-

8、+| sex | LCASE(job) |+-+-+| 1 | duck |+-+-+1 row in set (0.00 sec)3，F(xiàn)IND_IN_SET(str,strlist)如果字符串str在由N子串組成的表strlist之中，返回一個(gè)1到N的值。一個(gè)字符串表是被“,”分隔的子串組成的一個(gè)字符串。如果第一個(gè)參數(shù)是一個(gè)常數(shù)字符串并且第二個(gè)參數(shù)是一種類型為SET的列，F(xiàn)IND_IN_SET()函數(shù)被優(yōu)化而使用位運(yùn)算！如果str不是在strlist里面或如果 strlist是空字符串，返回0。如果任何一個(gè)參數(shù)是NULL，返回NULL。如果第一個(gè)參數(shù)包含一個(gè)“,”，該函數(shù)將工作不正常。看面是

9、二種不同的效果，可以看一下mysql SELECT id,name FROM string_test WHERE find_in_set(fly,hobby);+-+-+| id | name |+-+-+| 2 | zhang |+-+-+1 row in set (0.00 sec)mysql SELECT id,name FROM string_test WHERE hobby like fly%;+-+-+| id | name |+-+-+| 2 | zhang | 3 | ying |+-+-+2 rows in set (0.00 sec)4，F(xiàn)IELD(str,str1,str

10、2,str3,)返回str在str1, str2, str3, 清單的索引。如果str沒(méi)找到，返回0。FIELD()是ELT()反運(yùn)算。mysql SELECT id, name, FIELD( id, name, sex, job, hobby ) - FROM string_test where id SELECT id, name, ELT(1, id, name, sex, job, hobby ) FROM string_test where id SELECT id, name, ELT(2, id, name, sex, job, hobby ) FROM string_test

11、 where id SELECT id,REPLACE(hobby,firend,living) FROM string_test WHERE id = 2;+-+-+| id | REPLACE(hobby,firend,living) |+-+-+| 2 | fly,make living |+-+-+1 row in set (0.00 sec)7，REPEAT(str,count)返回由重復(fù)countTimes次的字符串str組成的一個(gè)字符串。如果count SELECT id,REPEAT(name,2) FROM string_test WHERE id 1 and id SELE

12、CT id,reverse(name) FROM string_test WHERE id 1 and id select id,name,INSERT(hobby,10,6,living) from string_test where id = 2;+-+-+-+| id | name | INSERT(hobby,10,6,living) |+-+-+-+| 2 | zhang | fly,make living |+-+-+-+1 row in set (0.00 sec)10，SUBSTRING(str FROM pos)從字符串str的起始位置pos返回一個(gè)子串。下面的sub2沒(méi)有值

13、，因?yàn)閙ysql數(shù)據(jù)庫(kù)的下標(biāo)是從1開(kāi)始的。mysql SELECT id, substring( hobby, 1, 6) AS sub1, substring( hobby from 0 for 8 ) AS sub2,substring( hobby,2) AS sub3, substring( hobby from 4 ) AS sub4 FROM string_test WHERE id =4;+-+-+-+-+-+| id | sub1 | sub2 | sub3 | sub4 |+-+-+-+-+-+| 4 | i love | | love you | ove you |+-+-

14、+-+-+-+1 row in set (0.00 sec)11,SUBSTRING_INDEX(str,delim,count)返回從字符串str的第count個(gè)出現(xiàn)的分隔符delim之后的子串。如果count是正數(shù)，返回最后的分隔符到左邊(從左邊數(shù)) 的所有字符。如果count是負(fù)數(shù)，返回最后的分隔符到右邊的所有字符(從右邊數(shù))。mysql SELECT id,SUBSTRING_INDEX(hobby,2) as test1,SUBSTRING_INDEX(hobby,-1) as test2 FROM string_test WHERE id = 3;+-+-+-+| id | tes

15、t1 | test2 |+-+-+-+| 3 | flying,driving | testing |+-+-+-+1 row in set (0.01 sec)12,LTRIM(str)返回刪除了其前置空格字符的字符串str。mysql SELECT id,LTRIM(job) FROM string_test WHERE id = 4;+-+-+| id | LTRIM(job) |+-+-+| 4 | love your love |+-+-+1 row in set (0.00 sec)13,RTRIM(str)返回刪除了其拖后空格字符的字符串str。mysql SELECT id,R

16、TRIM(job) FROM string_test WHERE id = 4;+-+-+| id | RTRIM(job) |+-+-+| 4 | love your love |+-+-+1 row in set (0.00 sec)14,TRIM(BOTH | LEADING | TRAILING remstr FROM str)返回字符串str，其所有remstr前綴或后綴被刪除了。如果沒(méi)有修飾符BOTH、LEADING或TRAILING給出，BOTH被假定。如果remstr沒(méi)被指定，空格被刪除。mysql select trim( test );+-+| trim( test ) |

17、+-+| test |+-+1 row in set (0.01 sec)mysql SELECT id,TRIM(LEADING love from job) as test1,TRIM(BOTH love from job) as test2,TRIM(TRAILING love from job) as test3 FROM string_test WHERE id =4 - ;+-+-+-+-+| id | test1 | test2 | test3 |+-+-+-+-+| 4 | your love | your | love your |+-+-+-+-+1 row in set

18、(0.00 sec)15,MID(str,pos,len)從字符串str返回一個(gè)len個(gè)字符的子串，從位置pos開(kāi)始。使用FROM的變種形式是ANSI SQL92語(yǔ)法。mysql SELECT id, mid( hobby, 1, 6 ) AS sub1, mid( hobby - FROM 0 - FOR 8 ) AS sub2, mid( hobby, 2 ) AS sub3, mid( hobby - FROM 4 ) AS sub4 - FROM string_test - WHERE id =4 ;+-+-+-+-+-+| id | sub1 | sub2 | sub3 | sub4

19、 |+-+-+-+-+-+| 4 | i love | | love you | ove you |+-+-+-+-+-+1 row in set (0.00 sec)mysql16，LPAD(str,len,padstr)返回字符串str，左面用字符串padstr填補(bǔ)直到str是len個(gè)字符長(zhǎng)。mysql SELECT id,LPAD(name,11,zhang ) FROM string_test WHERE id = 3;+-+-+| id | LPAD(name,11,zhang ) |+-+-+| 3 | zhang zying |+-+-+1 row in set (0.00 se

20、c)17,RPAD(str,len,padstr)返回字符串str，右面用字符串padstr填補(bǔ)直到str是len個(gè)字符長(zhǎng)。mysql SELECT id,RPAD(name,11, ying) FROM string_test WHERE id = 2;+-+-+| id | RPAD(name,11, ying) |+-+-+| 2 | zhang ying |+-+-+1 row in set (0.00 sec)18,LEFT(str,len)返回字符串str的最左面len個(gè)字符。mysql SELECT id,left(job,4) FROM string_test WHERE id

21、 = 4;+-+-+| id | left(job,4) |+-+-+| 4 | love |+-+-+1 row in set (0.00 sec)19,RIGHT(str,len)返回字符串str的最右面len個(gè)字符。mysql SELECT id,right(job,4) FROM string_test WHERE id = 4;+-+-+| id | right(job,4) |+-+-+| 4 | love |+-+-+1 row in set (0.00 sec)20,位置控制函數(shù)POSITION(substr IN str)返回子串substr在字符串str第一個(gè)出現(xiàn)的位置，如

22、果substr不是在str里面，返回0.LOCATE(substr,str,pos)返回子串substr在字符串str第一個(gè)出現(xiàn)的位置，從位置pos開(kāi)始。如果substr不是在str里面，返回0。INSTR(str,substr)返回子串substr在字符串str中的第一個(gè)出現(xiàn)的位置。這與有2個(gè)參數(shù)形式的LOCATE()相同，除了參數(shù)被顛倒。mysql SELECT id,INSTR(job,you) as instr,LOCATE(love,job,3) as locate,POSITION(love in job) as position FROM string_test WHERE id

23、 = 4;+-+-+-+-+| id | instr | locate | position |+-+-+-+-+| 4 | 6 | 11 | 1 |+-+-+-+-+1 row in set (0.00 sec)21，得到字符串長(zhǎng)度的函數(shù)LENGTH(str),OCTET_LENGTH(str),CHAR_LENGTH(str),CHARACTER_LENGTH(str)mysql SELECT id,LENGTH(job) as one,OCTET_LENGTH(job) as two,CHAR_LENGTH(job) as three,CHARACTER_LENGTH(job) as four FROM string_test WHERE id = 4;+-+-+-+-+-+| id | one | two | three | four |+-+-+-+-+-+| 4 | 14 | 14 | 14 | 14 |+-+-+-+-+-+1 row in set (0.00 sec)22，