Home | 簡體中文 | 繁體中文 | 雜文 | 打賞(Donations) | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 知乎專欄 | Search | Email

27.3. String

27.3.1. LEFT/RIGHT

LEFT(str,len)

mysql> select left(concat('1','0000000'),5) as number;
+--------+
| number |
+--------+
| 10000  |
+--------+
1 row in set (0.00 sec)
			

RIGHT(str,len)

mysql> select right(concat('0000000','1'),5) as number;
+--------+
| number |
+--------+
| 00001  |
+--------+
1 row in set (0.00 sec)
			

27.3.2. RPAD/LPAD

補齊長度用'0'填充

RPAD(str,len,padstr)
mysql> select rpad('10',5,'0') as txt;
+-------+
| txt   |
+-------+
| 10000 |
+-------+
1 row in set (0.01 sec)
			
LPAD(str,len,padstr)
mysql> select lpad('10',5,'0') as txt;
+-------+
| txt   |
+-------+
| 00010 |
+-------+
1 row in set (0.00 sec)
			

27.3.3. CONCAT

CONCAT(str1,str2,...)

mysql> select concat('Neo',' ','Chen') as Name;
+----------+
| Name     |
+----------+
| Neo Chen |
+----------+
1 row in set (0.00 sec)
			

27.3.4. GROUP_CONCAT

mysql> select GROUP_CONCAT(CONVERT( username , CHAR (16)) order by username desc) as username from test;
+-------------------------------------------+
| username                                  |
+-------------------------------------------+
| jam,jam2,john,john2,john3,neo,neo1,neo2   |
+-------------------------------------------+
6 rows in set, 1 warning (0.01 sec)
			

27.3.5. replace

select replace(goods_desc,':8000','') from ecs_goods;

update ecs_goods set goods_desc=replace(goods_desc,':8000','');
			

27.3.6. SUBSTRING

			
mysql> SELECT SUBSTRING('netkiller',4,4);   
+----------------------------+
| SUBSTRING('netkiller',4,4) |
+----------------------------+
| kill                       |
+----------------------------+
1 row in set (0.00 sec)			
			
			

與left,right 相同的用法

select right('M2014030615410572307:DEPOSIT', 7);
SELECT SUBSTRING('M2014030615410572307:DEPOSIT', -7);			
			

27.3.7. SUBSTRING_INDEX

SELECT SUBSTRING_INDEX('M2014030615410572307:DEPOSIT', ':', -1);
SELECT SUBSTRING_INDEX('M2014030615410572307:DEPOSIT', ':', 1);			
			

27.3.8. AES_ENCRYPT / AES_DECRYPT

簡單用法

			
mysql> select AES_ENCRYPT('helloworld','key');
+---------------------------------+
| AES_ENCRYPT('helloworld','key') |
+---------------------------------+
|                                 |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select AES_DECRYPT(AES_ENCRYPT('helloworld','key'),'key');
+----------------------------------------------------+
| AES_DECRYPT(AES_ENCRYPT('helloworld','key'),'key') |
+----------------------------------------------------+
| helloworld                                         |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql>
			
			

加密數據入庫

			
CREATE TABLE `encryption` (
	`mobile` VARBINARY(16) NOT NULL,
	`key` VARCHAR(32) NOT NULL
)
ENGINE=InnoDB;

INSERT INTO encryption(`mobile`,`key`)VALUES( AES_ENCRYPT('13691851789',md5('13691851789')), md5('13691851789')) 
select AES_DECRYPT(mobile,`key`), length(mobile) from encryption;