python mysql 匹配字符串_当Python字符串遇上MySQL

学习的时候我喜欢对比，MySQL和Oracle比，Python和MySQL比，总能有一些收获，也有了新的理解。

今天整理这部分内容的时候，我发现Python和MySQL还是有很多相似之处。学习一门语言，一个数据库，字符串的处理都是一个相对重要的部分，所以我决定对比一下两者的差别。

下面的演示会一边Python,一边MySQL,所以按照这个思路来看就不会感觉突兀了。

转义字符

>>> print '\\'

\

mysql> select '\\';

+---+

| \ |

+---+

| \ |

+---+

>>> print '\"'

"

mysql> select '\"';

+---+

| " |

+---+

| " |

+---+

>>> print '\''

'

mysql> select '\'';

+---+

| ' |

+---+

| ' |

+---+

字符串拼接

>>> x = 'hello'

>>> y = 'tester'

>>> z = x + y

>>> print z

hellotester

set @x='hello';

set @y='tester';

mysql> select @x;

+-------+

| @x    |

+-------+

| hello |

mysql> select @y;

+--------+

| @y     |

+--------+

| tester |

+--------+

mysql> select concat(@x,@y);

+---------------+

| concat(@x,@y) |

+---------------+

| hellotester   |

+---------------+

字符串复制

>>> print '#'*20

####################

mysql> select repeat('#',20);

+----------------------+

| repeat('#',20)       |

+----------------------+

| #################### |

+----------------------+

>>> print ' '*20 + 'end'

end

mysql> select space(20);

+----------------------+

| space(20)            |

+----------------------+

|                      |

+----------------------+

字符串截取

>>> name = 'yangjianrong'

>>> name[0]

'y'

>>> name[-1]

'g'

>>> name[1]

'a'

>>> name[1:4]

'ang'

>>> name[:]

'yangjianrong'

>>>

>>> name[1:4:2]

'ag'

mysql> set @name:='yangjianrong';

mysql> select left(@name,1);

+---------------+

| left(@name,1) |

+---------------+

| y             |

+---------------+

mysql> select right(@name,1);

+----------------+

| right(@name,1) |

+----------------+

| g              |

+----------------+

mysql> select substring(@name,2,3);

+----------------------+

| substring(@name,2,3) |

+----------------------+

| ang                  |

+----------------------+

mysql> select substring(@name,1);

+--------------------+

| substring(@name,1) |

+--------------------+

| yangjianrong       |

+--------------------+

或者使用mid

mysql> select mid(@name,2,3);

+----------------+

| mid(@name,2,3) |

+----------------+

| ang            |

+----------------+

mysql>  select mid(@name,1);

+--------------+

| mid(@name,1) |

+--------------+

| yangjianrong |

+--------------+

>>> name

'yangjianrong'

>>> print '%s' %name

yangjianrong

字符串格式化，匹配

>>> '{name},{alias}'.format(name='yangjianrong',alias='jeanron100')

'yangjianrong,jeanron100'

>>>

mysql>  select concat(insert(@name,1,4,'yangjianrong'),insert(@alias,1,5,'jeanron100')) comm;

+------------------------+

| comm                   |

+------------------------+

| yangjianrongjeanron100 |

+------------------------+

字符串长度>>> ba

'this is a test bar'

>>> len(ba)

18

mysql> select length(@ba);

字符串空格处理

>>> s = ' abc '

>>> s.lstrip()

'abc '

>>> s.rstrip()

' abc'

>>> s.strip()

'abc'

>>>

mysql> set @s=' abc ';

Query OK, 0 rows affected (0.00 sec)

mysql> select ltrim(@s);

+-----------+

| ltrim(@s) |

+-----------+

| abc       |

+-----------+

1 row in set (0.00 sec)

mysql> select rtrim(@s);

+-----------+

| rtrim(@s) |

+-----------+

|  abc      |

+-----------+

1 row in set (0.00 sec)

mysql> select trim(@s);

+----------+

| trim(@s) |

+----------+

| abc      |

+----------+

1 row in set (0.00 sec)

字符串匹配

>>> l = ['a','b','c']

>>> ''.join(l)

'abc'

>>> '*'.join(l)

'a*b*c'

mysql> select concat_ws(',','a','b','c','d','e') comm;

+-----------+

| comm      |

+-----------+

| a,b,c,d,e |

+-----------+

>>> s = 'a b c d e '

>>> s.split(' ')

['a', 'b', 'c', 'd', 'e', '']

mysql> set @s='a b c d e ';

Query OK, 0 rows affected (0.00 sec)

mysql> select replace(@s,' ',',');

+---------------------+

| replace(@s,' ',',') |

+---------------------+

| a,b,c,d,e,          |

+---------------------+

字符串复制

>>> s = 'aabbcc'

>>> s.replace('aa','tt')

'ttbbcc'

mysql> set @s='aabbcc';

Query OK, 0 rows affected (0.00 sec)

mysql> select replace(@s,'aa','tt');

+-----------------------+

| replace(@s,'aa','tt') |

+-----------------------+

| ttbbcc                |

+-----------------------+

字符串编码

>>> s.encode('utf8')

'aabbcc'

mysql> select  convert(@s using utf8);

+------------------------+

| convert(@s using utf8) |

+------------------------+

| aabbcc                 |

+------------------------+

判断字符串开始匹配的字符

>>> s.startswith('aa')

True

mysql> SELECT LOCATE('aa',@s,1);

+-------------------+

| LOCATE('aa',@s,1) |

+-------------------+

|                 1 |

+-------------------+