很多时候，我们需要在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 提示 Field * doesn't have a default value 的解决办法
解决办法在 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然后重启 MySQLSTRICT_TRANS_TABLES 存储引擎启用严格模式，非法数据值被拒绝出现此问题的原因在 MySQL 5.0.2 以前，MySQL 对非法值检查并不严厉，而且为了数据输入还会强制将他们变为合法值。在 MySQL 5.0.2 以后的版本中，保留了以前的默认行为，但你可以为不良值选择更传统的处理方法，从而使得服务器能够拒绝并放弃出现不良值的语句。严格模式如果未使用严格模式，下面的情况是合法的：如果将不正确的值插入到列，如将 null 值插入非 null 列，或将过大的数据插入数值列，MySQL 会将这些列设置为最可能的值，而不是抛出错误信息。如果视图将超过范围的值保存到数值列，MySQL 服务器将保存 0 (最小的可能值) 取而代之，或最大的可能值。对于字符串，MySQL 或保存空字符串，或将字符串可能多的部分保存到列中。如果打算将不是以数值开头的字符串保存到数值列，MySQL 将保存 0 。MySQL 允许将特定的不正确日期值保存到 DATE 和 DATETIME 列（如：2000-02-31 或 2000-02-00）。其观点在于，验证日期不是 SQL 服务器的任务。如果 MySQL 能保存日期值并准确检索相同的值，MySQL 就能按给定的值保存它。如果日期完全不正确（超出服务器能保存的范围）将在列中保存特殊的日期值 0000-00-00 取而代之。如果视图将 null 值保存到不接受 null 值的列，对于单行 insert 语句，将出现错误。对于多行 insert 语句或者 insert into ... select 语句，MySQL 服务器会保存针对列数据类型的隐含默认值。一般情况下，对于数值类型，它是 0，对于字符串类型，它是空字符串（''），对于日期和时间类型是 zero 。如果 insert 语句未为列指定值，如果列定义包含明确的 default 子句，MySQL 将插入默认值。如果在定义中没有这类 default 子句，MySQL 会插入列数据类型的隐含默认值。采用前描述规则的原因在于，在语句开始执行前，无法检查这些情况。如果在更新了数行后遇到这类问题，我们不能仅靠回滚解决，这是因为存储引擎可能不支持回滚。中止语句并不是良好的选择，在该情况下，更新完成了 “一半”，这或许是最差的情况。对于本例，较好的方式是 “尽可能做到最好”，然后就像什么都没有发生那样继续执行。在 MySQL 5.0.2 以后的版本中，可以使用 STRICT_TRANS_TABLES 或 STRICT_ALL_TABLES SQL 模式，选择更严格的处理方式。STRICT_TRANS_TABLES的工作方式：对于事务性存储引擎，在语句中任何地方出现的不良数据值均会导致放弃语句并执行回滚。对于非事务性存储引擎，如果错误出现在要插入或更新的第 1 行，将放弃语句。（在这种情况下，可以认为语句未改变表，就像事务表一样）。首行后出现的错误不会导致放弃语句。取而代之的是，将调整不良数据值，并给出告警，而不是错误。换句话讲，使用 STRICT_TRANS_TABLES 后，错误值会导致 MySQL 执行回滚操作，如果可以，所有更新到此为止。要想执行更严格的检查，请启用 STRICT_ALL_TABLES。除了非事务性存储引擎，它与 STRICT_TRANS_TABLES 等同，即使当不良数据出现在首行后的其他行，所产生的错误也会导致放弃语句。这意味着，如果错误出现在非事务性表多行插入或更新过程的中途，仅更新部分结果。前面的行将完成插入或更新，但错误出现点后面的行则不然。对于非事务性表，为了避免这种情况的发生，可使用单行语句，或者在能接受转换警告而不是错误的情况下使用 STRICT_TRANS_TABLES。要想在第 1 场合防止问题的出现，不要使用 MySQL 来检查列的内容。最安全的方式（通常也较快）是，让应用程序负责，仅将有效值传递给数据库。有了严格的模式选项后，可使用 INSERT IGNORE 或 UPDATE IGNORE 而不是不带 IGNORE 的 INSERT 或 UPDATE，将错误当作告警对待。
相同表结构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(); }其他方法$pdo->beginTransaction(); try{ // quantity 为请求减掉的库存数量 $pdo->query('update s_store set amount = amount - quantity where postID = 12345'); $result = $pdo->query('select amount from s_store where postID = 12345'); if($result->amount < 0){ // 抛出异常 throw new \PDOException('库存不足'); } // 没有错误则提交事务 $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;确保 quantity 在事务中读取的数据是可靠的。UPDATE products SET quantity = '1' WHERE id = 3; COMMIT WORK;提交 (Commit) 事务，products 解锁。Tips:BEGIN / COMMIT 为事务的起始及结束点；在事务进行当中，只有 SELECT ... FOR UPDATE 或 LOCK IN SHARE MODE 会等待事务结束后才执行，一般的 SELECT ... 则不受此影响。简单用法为了防止处理不当，根据业务实际情况，如果我们需要先查询，后更新数据的话，可以这样使用语句：UPDATE products SET quantity = '1' WHERE id = 3 AND quantity > 0;
事务处理事务 (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。除了登记资料，还需要记录工资。<?php try { // 开启事务处理 $pdo->beginTransaction(); // 执行 SQL $sql1 = $pdo->exec("insert into staff (id, first, last) values (23, 'Joe', 'Bloggs')"); if (!$sql1) { // 抛出异常 throw new \PDOException("登记资料失败"); } $sql2 = $pdo->exec("insert into salarychange (id, amount, changedate) values (23, 50000, NOW())"); if (!$sql2) { // 抛出异常 throw new \PDOException("记录工资失败"); } // 没有错误则提交事务 $pdo->commit(); } catch (\PDOException $e) { // 遇到错误则回滚事务 $pdo->rollBack(); echo "Failed: " . $e->getMessage(); }