mysql数据库操作记录持续更新...

Error: ER_LOCK_WAIT_TIMEOUT: Lock wait timeout exceeded; try restarting transaction 锁等待超时。是当前事务在等待其它事务释放锁资源造成的。

遇到场景：

transaction没有commit或者释放，造成的锁表，不能在进行更新操作

解决方法：执行 select mysql_thread_id from information_schema.innodb_trx 找到线程号，执行KILL命令：KILL 线程id

1.查看删除数据库表的唯一约束

SHOW INDEX FROM tbl_name （唯一约束也是索引）

ALTER TABLE tbl_name DROP INDEX index_name

2.update where中不能使用子查询，解决方案如下：

将子查询作为一张表放在前面

UPDATE `areainfo` a,  (

    SELECT code  FROM `areainfo` where level = 1

) b SET a.`level` =2  WHERE a.`parent_code`  = b.code

UPDATE `areainfo` a,  (

    SELECT code, name  FROM `areainfo` where level = 1

) b SET a.`fullName` =concat(b.name,',',a.name) WHERE a.`parentCode`  = b.code and a.level = 2

UPDATE `areainfo` a,  (

    SELECT code, name,parentCode  FROM `areainfo` where level = 2

) b , (

    SELECT code, name  FROM `areainfo` where level = 1

) c SET a.`fullName` =concat(c.name,',',b.name,',',a.name) WHERE a.`parentCode`  = b.code and b.`parentCode`  = c.code and a.level = 3

3.批量更新遇到唯一约束的字段，比如index字段批量减1，这个石斛需要加上order by来说明更新顺序，如果不按顺序会提示重复错误。

4.一张表更新另一张表

 UPDATE  `companyBasicComponentBom` t1,   `BomTemple` t2 set t1.wastageRate = t2.wastageRate  
 WHERE t1.`bComponentId` = t2.`bComponentId` AND t1.`bParentId` = t2.`bParentId`

5. 正则匹配字符串，字符串的拼接

INSERT INTO `companySalesProduct`( `companyId`, `customerId`, `customerMolds`, `componentId`, `unitPrice`, `currencyId`, `referProfitMargin`, `customerProductName`, `extendDesc`, `stateId`, `userId`)

SELECT 16, 62, concat((right(substring_index(aliasName, '-', 1), 8)), '-', left(substring_index(aliasName, '-', -1), 2)) , id, 0, 1, 0, '', '', 1, 1788 FROM `companyBasicComponent` where companyId = 16 and aliasName REGEXP '[0-9]{8}-[0-9]{2}' and aliasName not like '%停用%'

秒客网

mysql数据库操作记录持续更新...

相关文章