很多时候，我们需要在sql里面直接解析json字符串。这里针对mysql5.7版本的分水岭进行区分。

1.对于mysql5.7以上版本

使用mysql的内置函数JSON_EXTRACT(column, '$.key')，这个函数有两个参数，第一个参数column代表json列的列名；第二个参数key代表json字符串中的某一个key。

SELECT JSON_EXTRACT('{"priceTag":"员工/合作关键人","priceDiscount":"90"}', '$.priceDiscount') AS '定价折扣';

对于简单的json字符串肯定是可以解析成功，但是对于嵌套数组的没试过。

2.对于mysql5.7以下版本

只能充分发挥已有函数的功能去截取实现，无论实现方式是存储过程还是简单的sql语句，其原理都是一样的。

第一步，将所有的花括号的闭括号'}'替换成英文逗号','；第二步，获取key的坐标keyIndex和长度keyLength；第三步，获取以key为起点，第一个英文逗号','的坐标symbolIndex；第四步，使用substring截取字符串SUBSTRING(targetJsonStr, keyIndex + keyLength, symbolIndex - keyIndex - keyLength)；第五步，使用replace将双引号'"'替换成空字符串''，完工。

示例：从{"priceTag": "员工/合作关键人","priceDiscount": "90"}中获取priceDiscount的值。

SELECT REPLACE
(
    SUBSTRING(
        REPLACE('{"priceTag":"员工/合作关键人","priceDiscount":"90"}','}',','),
        LOCATE('priceDiscount":',REPLACE('{"priceTag":"员工/合作关键人","priceDiscount":"90"}','}',','))
            + CHAR_LENGTH('priceDiscount":'),
        LOCATE(
            ',',
            REPLACE('{"priceTag":"员工/合作关键人","priceDiscount":"90"}','}',','),
            LOCATE('priceDiscount":',REPLACE('{"priceTag":"员工/合作关键人","priceDiscount":"90"}','}',','))
                + CHAR_LENGTH('priceDiscount":')
        ) - (
            LOCATE('priceDiscount":',REPLACE('{"priceTag":"员工/合作关键人","priceDiscount":"90"}','}',','))
                + CHAR_LENGTH('priceDiscount":')
        )
    ),'"',''
) AS '定价折扣';

如何根据根据父id递归查询所有子孙id，废话就不多说，直接上干货。

SELECT id    
FROM (    
SELECT t1.id, IF ( FIND_IN_SET(pid, @pids) > 0, @pids := CONCAT(@pids, ',', id), 0) AS ischild    
FROM (SELECT id, pid    
           FROM category t    
           ORDER BY pid, id    
           ) t1,    
           (SELECT @pids := '1') t2    
) t3    
WHERE ischild != 0 OR id = '1'

其中id为查询id，pid为父id，category为表名，'1'为要查询的id值。最后可以加上OR id = '1' 表示查询结果包括自己，也可以不加。

这样就一次性查完id为1的所在子、孙id，在文章分类查询用处较大。

解决办法

在 MySQL 5.7 以上版本中，启用了严格模式。

在配置文件中 /etc/mysql/my.cnf 中找到：

sql-model=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

修改为：

sql-mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

然后重启 MySQL

STRICT_TRANS_TABLES 存储引擎启用严格模式，非法数据值被拒绝

出现此问题的原因

在 MySQL 5.0.2 以前，MySQL 对非法值检查并不严厉，而且为了数据输入还会强制将他们变为合法值。

在 MySQL 5.0.2 以后的版本中，保留了以前的默认行为，但你可以为不良值选择更传统的处理方法，从而使得服务器能够拒绝并放弃出现不良值的语句。

严格模式

如果未使用严格模式，下面的情况是合法的：

- 阅读剩余部分 -

相同表结构

INSERT INTO table1 SELECT * FROM table2;

不同表结构

INSERT INTO table1(filed1, ..., filedn) SELECT table2.filed1, ..., table2.filedn FROM table2;

不同数据库

INSERT into db1.table1(id, number, name) 
  SELECT stu.person_id
     , stu.number
     , person.name
FROM db2.t_stu_info AS stu
    , db2.t_person_info as person
    WHERE stu.person_id = person.id

问题描述

一般电子商务网站都会遇到如团购、秒杀、特价之类的活动，而这样的活动有一个共同的特点就是访问量激增、上千甚至上万人抢购一个商品。

然而，作为活动商品，库存肯定是很有限的，如何控制库存不让出现超卖，以防止造成不必要的损失，是众多电子商务网站程序员头疼的问题，这同时也是最基本的问题。

条件

总库存：4个商品
请求人：a、1个商品 b、2个商品 c、3个商品

错误示例

$pdo->beginTransaction();
try{
    $result = $pdo->query('select amount from s_store where postID = 12345');
    if($result->amount > 0){
        // quantity 为请求减掉的库存数量
        $pdo->query('update s_store set amount = amount - quantity where postID = 12345');
    }
    // 没有错误则提交事务
    $pdo->commit();
}catch($e \PDOException){
    // 遇到错误则回滚事务
    $pdo->rollBack();
    echo "Failed: " . $e->getMessage();
}

使用 SELECT ... FOR UPDATE

$pdo->beginTransaction();
try{
    // 使用 SELECT ... FOR UPDATE 将此行数据锁住，在提交事务或者回滚事务时自动解开。
    $result = $pdo->query('select amount from s_store where postID = 12345 for update');
    if($result->amount < 0){
        // 抛出异常
        throw new \PDOException('库存不足');
    }
    // quantity 为请求减掉的库存数量
    $pdo->query('update s_store set amount = amount - quantity where postID = 12345');
    // 没有错误则提交事务
    $pdo->commit();
}catch($e \PDOException){
    // 遇到错误则回滚事务
    $pdo->rollBack();
    echo "Failed: " . $e->getMessage();
}

其他方法

- 阅读剩余部分 -

在工作中，我们经常会遇到这样的问题，需要更新库存，当我们查询到可用的库存准备修改时，这时，其他的用户可能已经对这个库存数据进行修改了，导致，我们查询到的数据会有问题，下面我们就来看解决方法。

如果SELECT 后面若要UPDATE 同一个表单，最好使用SELECT ... UPDATE。

举个例子

假设商品表单 products 内有一个存放商品数量的 quantity，在订单成立之前必须先确定 quantity 商品数量是否足够( quantity > 0 )，然后才把数量更新为 1 。

SELECT quantity FROM products WHERE id = 3; UPDATE products SET quantity = 1 WHERE id = 3;

开启事务

为什么不安全呢？

高并发时会出现问题，如果我们需要在 quantity > 0 的情况下才能扣库存，假设程序在第一行 SELECT 读到的 quantity 是 2，但是当 MySQL 正准备要 UPDATE 的时候，可能已经有人把库存扣成 0 了，但是程序却浑然不知，将错就错的 UPDATE 下去了。因此必须利用事务机制来确保读取及提交的数据都是正确的。

BEGIN WORK; SELECT quantity FROM products WHERE id = 3 FOR UPDATE;

此时 products 数据中 id = 3 的数据被锁住，其它事务必须等待此次事务提交后才能执行。

SELECT * FROM products WHERE id = 3 FOR UPDATE;

- 阅读剩余部分 -

事务处理

事务 (transaction) 是由查询和/或更新语句的序列组成。用 begin、start transaction 开始事务，rollback 回滚事务，commit 提交事务。

在开始事务后，可以有若干个 SQL 查询或更新语句，每个 SQL 递交执行后，还应该有判断是否正确执行的语句，以确定下一步是否回滚，若都被正确执行则最后提交事务。

事务一旦回滚，数据库则保持开始事务前状态。就好象一个被编辑的文件不存盘退出，自然还是保持文件原来的样子。

所以，事务可被视为原子操作，事务中的 SQL，要么全部执行，要不一句都不执行。

如果需要一个事务，则必须用 PDO::beginTransaction() 方法来启动，一旦开始了事务，可用 PDO::commit() 或 PDO::rollBack()来完成，这取决于事务中的代码是否运行成功。

Tips: MySQL只有 InnoDB 驱动支持事务处理，默认 MyIsAM 驱动不支持。

代码示例

连接数据库：

<?php
try {
    // 数据库 PDO 连接
    $pdo = new \PDO('mysql:host=localhost;dbname=mydb', 'root', 'root', array(PDO::ATTR_PERSISTENT => true));
    // 开启异常处理
    $pdo->setAttribute(PDO::ATTR_ERRMODE,  PDO::ERRMODE_EXCEPTION);
} catch (\PDOException $e) {
    echo "数据库连接失败：".$e->getMessage();
    exit;
}

事务处理：
在下面例子中，假设为新员工创建一组条目，分配一个为 23 的 ID。除了登记资料，还需要记录工资。

- 阅读剩余部分 -