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)
			

补齐长度用'0'填充

mysql> select rpad('10',5,'0') as txt;
+-------+
| txt   |
+-------+
| 10000 |
+-------+
1 row in set (0.01 sec)
			

mysql> select lpad('10',5,'0') as txt;
+-------+
| txt   |
+-------+
| 00010 |
+-------+
1 row in set (0.00 sec)
			

CONCAT(str1,str2,...)

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

			
SELECT CONCAT_WS(',', 'Neo', 'Chen');
Neo,Chen

SELECT CONCAT_WS('-', 'Neo', 'Chen');
Neo-Chen			
			
			

使用逗号链接字符串

			
SELECT 
    CONCAT_WS(',', id, name, age)
FROM
    mytable			
			
			

当我使用 select CONCAT_WS(",", *) as string from tab 时发现不支持 * 操作。

解决方案如下

			
SET @column = NULL;

SELECT 
    GROUP_CONCAT(COLUMN_NAME) AS fields INTO @column
FROM
    INFORMATION_SCHEMA.Columns
WHERE
    table_name = 'mytable'
        AND table_schema = 'test';
        
-- select @column;

SET @sql = CONCAT('SELECT CONCAT_WS(",",',@column, ' )  FROM mytable');

select @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
			
			

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)
			

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

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

			
update testing.consumer set avatar = replace(avatar, "http://oss.netkiller.cn","");
SELECT * FROM testing.consumer;
			
			

			
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);			
			

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

简单用法

			
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;