MySQL文本处理函数2_20160921

 需求：从目前的 test_a03order 表里面提取出来产品规格,押金的数据

一.首先添加表字段
我们在表里面添加这两个字段 命名为product_size,deposit 后期进行更新这两个字段内容就可以了

ALTER TABLE test_a03order ADD product_size VARCHAR(255) AFTER productname;#在productname之后插入规格字段
ALTER TABLE test_a03order ADD deposit VARCHAR(255) AFTER price;#在price之后插入押金带元字 仅此varchar 数据类型

插入字段成功

二、分析需求属性

1、发现产品规格,押金数据是存在于productname中的因此我们需要先看数据表内有多少个product 观察所有产品的命名规律

1 SELECT productname FROM test_a03order GROUP BY productname

结果在这个表里有4个product 分别为 "心灵鸡汤（押瓶1000元）10000ml*24瓶/箱”,”横刀夺爱丸（押瓶888元）15粒/盒","脑残片（押瓶99元）15片/盒","鬼迷心窍汤（押瓶888元）10000ml*12瓶/箱","鬼迷心窍汤（押瓶888元）10000ml*12瓶/箱",通过观察productname都包含一个"押"字,product_size是在字符串的最后一个字,deposit位于中间的括号里且在"瓶"字和"元"中间 因此可以使用 LEFT() RIGHT() CHAR_LENGTH()函数进行处理 。

2、获取product_size

SELECT productname,RIGHT(productname,1) AS product_size
FROM test_a03order
WHERE productname LIKE "%押%"

3、获取deposit 

# 我们一步一步来，先取出"瓶"字左面的左边字段(包含瓶这个字符串本身),再取出"元"字左面的左边字段(包含元这个字符串本身)

SELECT productname,
LEFT(productname,INSTR(productname,"瓶"))AS deposit_left,
LEFT(productname,INSTR(productname,"元")) AS deposit_leftall
FROM test_a03order
WHERE productname LIKE "%押%"

     这时候发现deposit_leftall 包含deposit_left字符串的全部,并且差异就在押金数值,且位于deposit_leftall 字段的右面部分,用deposit_leftall减去deposit_left不就是押金呢 但字符串类型不能加减呢，因此我们需要先计算上面deposit_left,deposit_leftall的长度 用它们的长度之差 作为right()函数的参数 截取 deposit_leftall

我们为什么用char_length()而不用length()函数截取字符长度就在于length()默认一个汉字长度为3,CHAR_LENGTH()不论中英文都默认长度为1

所以现在重新写下SQL 

SELECT productname,
LEFT(productname,INSTR(productname,"瓶")) AS deposit_left,
LEFT(productname,INSTR(productname,"元")) AS deposit_leftall,
CHAR_LENGTH(LEFT(productname,INSTR(productname,"瓶"))) AS deposit_left_length,
CHAR_LENGTH(LEFT(productname,INSTR(productname,"元"))) AS deposit_leftall_length,
RIGHT(LEFT(productname,INSTR(productname,"元")),CHAR_LENGTH(LEFT(productname,INSTR(productname,"元")))-CHAR_LENGTH(LEFT(productname,INSTR(productname,"瓶")))) AS deposit
#上面right()这么长代码实质上就是right(deposit_leftall,deposit_leftall_length-deposit_left_length) 
FROM test_a03order
WHERE productname LIKE "%押%"

重新整理以下代码把 product_size 和deposit都省去中间过程

SELECT productname,
RIGHT(productname,1) AS product_size,
RIGHT(LEFT(productname,INSTR(productname,"元")),CHAR_LENGTH(LEFT(productname,INSTR(productname,"元")))-CHAR_LENGTH(LEFT(productname,INSTR(productname,"瓶")))) AS deposit
FROM test_a03order
WHERE productname LIKE "%押%"

 三、更新数据表插入的两个字段 

 更新字段内容

UPDATE test_a03order SET product_size=RIGHT(productname,1) WHERE productname LIKE "%押%";
UPDATE test_a03order SET `deposit`=RIGHT(LEFT(productname,INSTR(productname,"元")),CHAR_LENGTH(LEFT(productname,INSTR(productname,"元")))-CHAR_LENGTH(LEFT(productname,INSTR(productname,"瓶")))) WHERE productname LIKE "%押%"

更新成功 执行查询查看效果

SELECT * FROM test_a03order

四、更新成功