一.操作sql語(yǔ)句時(shí)報(bào)錯(cuò).

• Lock wait timeout exceeded; try restarting transaction --這個(gè)意思是鎖沒(méi)有釋放,超時(shí)了
  解決方案:
  1.是不是打了斷點(diǎn)一直沒(méi)有放行 導(dǎo)致事務(wù)沒(méi)有提交
  2.我們可以通過(guò)到information_schema 中來(lái)進(jìn)行查找被鎖的語(yǔ)句。
  解釋： information_schema這張數(shù)據(jù)表保存了MySQL服務(wù)器所有數(shù)據(jù)庫(kù)的信息。如數(shù)據(jù)庫(kù)名，數(shù)據(jù)庫(kù)的表，表欄的數(shù)據(jù)類型與訪問(wèn)權(quán)限等。再簡(jiǎn)單點(diǎn)，這臺(tái)MySQL服務(wù)器上，到底有哪些數(shù)據(jù)庫(kù)、各個(gè)數(shù)據(jù)庫(kù)有哪些表，每張表的字段類型是什么，各個(gè)數(shù)據(jù)庫(kù)要什么權(quán)限才能訪問(wèn)，等等信息都保存在information_schema表里面
  3.我們可以用下面三張表來(lái)查原因：
  innodb_trx 當(dāng)前運(yùn)行的所有事務(wù)
  innodb_locks 當(dāng)前出現(xiàn)的鎖
  innodb_lock_waits 鎖等待的對(duì)應(yīng)關(guān)系
  如果數(shù)據(jù)庫(kù)中有鎖的話，我們可以使用這條語(yǔ)句來(lái)查看：

select * from information_schema.innodb_trx

LOCK WAIT為占用系統(tǒng)資源的語(yǔ)句，我們需要?dú)⒌暨@個(gè)鎖，執(zhí)行 kill 線程id號(hào)。上面這條記錄的id為199120823069, trx_mysql_thread_id 為 738178711, 所以我們執(zhí)行：kill 738178711殺掉這個(gè)MySQL語(yǔ)句的線程即可。
4、終極方法
如果以上方法殺掉線程，但還是不能解決，則我們就可以查找執(zhí)行線程用時(shí)比較久的用戶，然后直接干掉。

    SELECT * from information_schema.`PROCESSLIST` WHERE Time > 1000 AND USER = 'wonguser' ORDER BY TIME desc;
    kill 740097562

這樣把所有耗時(shí)比較久的任務(wù)干掉，就可以解決這個(gè)問(wèn)題了。

mysql導(dǎo)入sql文件超時(shí),報(bào)錯(cuò)

解決:

show VARIABLES like '%max_allowed_packet%';

set global max_allowed_packet = 16*1024*1024*10

mysql使用navict導(dǎo)入報(bào)錯(cuò) 可以使用小海豚或者用cmd導(dǎo)入

使用cmd: https://blog.csdn.net/weixin_45764765/article/details/109387718?utm_medium=distribute.pc_relevant_bbs_down.none-task-blog-baidujs-1.nonecase&depth_1-utm_source=distribute.pc_relevant_bbs_down.none-task-blog-baidujs-1.nonecase

二. 操作svn報(bào)錯(cuò)

錯(cuò)誤內(nèi)容: Previous operation has not finished; run 'cleanup' if it was interr 進(jìn)入死循環(huán)了
解決: 先去官網(wǎng)下載sqlite3.exe(點(diǎn)擊下載)

2.https://blog.csdn.net/yu102655/article/details/83714524

三.需要添加字段進(jìn)行排序

例如: 我需要?jiǎng)?chuàng)建一個(gè)sort字段,利用sort進(jìn)行排序的話這個(gè)字段類型不要用varchar類型 用int或者

圖片.png

sql例子:

SELECT
    t.sc_id,    
    t.SORT, 
    ifNULL(firstName,towName) as firstName
    ,case  when firstName is null  then breed_name else towName end towName
    ,case  when firstName is null  then "" else breed_name end threeName
    ,t.breed_type,breed_unit,pid,PUTQUANTITY,ACTUAL,DISPATCHQUANTITY,CANCELQUANTITY
    ,   ifNULL(firstName,1) as firstType
FROM
    (
SELECT
  a.SORT,
    a.SC_ID,
    a.BREED_NAME,
    a.BREED_TYPE,
    a.BREED_UNIT,
    a.PID,
    ( SELECT a1.breed_name FROM SUPPLIES_CATEGORY_INFO a1 WHERE a1.sc_id = a.pid ) AS towName,
    (SELECT b1.breed_name FROM SUPPLIES_CATEGORY_INFO a1 LEFT JOIN SUPPLIES_CATEGORY_INFO b1 ON b1.sc_id = a1.pid WHERE a1.sc_id = a.pid ) AS firstName , 
    sum(c.INVENTORY_QUANTITY) as PUTQUANTITY, -- 入庫(kù)數(shù)量
    sum(c.ACTUAL_INVENTORY) as ACTUAL , -- 庫(kù)存數(shù)量
    sum(d.DELIVERY_QUANTITY) as DISPATCHQUANTITY, -- 調(diào)度出庫(kù)數(shù)量
    sum(e.DELIVERY_QUANTITY) as CANCELQUANTITY -- 核銷出庫(kù)數(shù)量
FROM
    SUPPLIES_CATEGORY_INFO a
    LEFT JOIN SUPPLIES_CATEGORY_INFO b ON a.sc_id = b.pid 
    LEFT JOIN ENTERPRISE_PUT_INFO c ON a.breed_name = c.breed and c.EW_ID = "1"  and c.MONTHLY BETWEEN "2020-08" and "2020-11"
    LEFT JOIN ENTERPRISE_OUT_INFO d ON a.breed_name = d.breed and d.OUT_TYPE = "1" and d.EUI_STATUS ="T"
    LEFT JOIN ENTERPRISE_OUT_INFO e ON a.breed_name = e.breed and e.OUT_TYPE = "2" and e.EUI_STATUS ="T"
WHERE
    a.pid IS NOT NULL 
    AND b.pid IS NULL
 GROUP BY   a.breed_name
 order by a.SORT asc
) as t