bitsCN.com
定时任务
查看event是否开启: show variables like '%sche%';
将事件计划开启: set global event_scheduler=1;
关闭事件任务: alter event e_test ON COMPLETION PRESERVE DISABLE;
开户事件任务: alter event e_test ON COMPLETION PRESERVE ENABLE;
简单实例.
创建表 CREATE TABLE test(endtime DATETIME);
创建存储过程test
CREATE PROCEDURE test ()
BEGIN
update examinfo SET endtime = now() WHERE id = 14;
END;
创建event e_test
CREATE EVENT if not exists e_test
on schedule every 30 second
on completion preserve
do call test();
CREATE EVENT if not exists e_test
on schedule every 1 second
on completion preserve
do insert into aa values (now());
每隔30秒将执行存储过程test,将当前时间更新到examinfo表中id=14的记录的endtime字段中去.
触发器
delimiter //
CREATE TRIGGER trigger_htmlcache BEFORE INSERT ON t_model
FOR EACH ROW BEGIN
if CURDATE()
end if;
END;
//
通过建表->Insert的方式测试.
DELIMITER $$
DROP PROCEDURE IF EXISTS `njfyedepartment`.`sp_ireport` $$
CREATE DEFINER=`root`@`%` PROCEDURE `sp_ireport`(IN qureyType VARCHAR(20),IN daytime VARCHAR(20),IN p_ids VARCHAR(50),IN c_ids VARCHAR(50),IN ct1_ids VARCHAR(50),IN ct2_ids VARCHAR(50),IN ku VARCHAR(50),IN ireport_chart varchar(50))
BEGIN
DECLARE i INT DEFAULT 1;
IF qureyType = 'insert' OR qureyType = 'INSERT' THEN
INSERT INTO ireport
(pid,cid,ct1id,ct2id,creatTime,crawlerNumber,WEEK)
SELECT province AS pid,
cityid AS cid,
category1id AS ct1id,
category2id AS ct2id,
(CURRENT_DATE) AS creatTime,COUNT(*) AS crawlerNumber,
(FLOOR(DAYOFMONTH(CURRENT_DATE)/8)+1) AS WEEK
FROM t_model t
WHERE TIME > (CURRENT_DATE-1) AND TIME AND province IS NOT NULL
AND cityid IS NOT NULL
GROUP BY province,cityid,category1id,category2id;
END IF;
IF qureyType = 'month' OR qureyType = 'MONTH' THEN
IF EXISTS(SELECT * FROM information_schema.`TABLES` T WHERE TABLE_NAME = 'tmp_result' AND TABLE_SCHEMA = ku) THEN
DROP TABLE tmp_result;
END IF;
CREATE TABLE tmp_result
(pid VARCHAR(50),pName VARCHAR(50),cid VARCHAR(50),cName VARCHAR(50),ct1id VARCHAR(50),ct1Name VARCHAR(50),
ct2id VARCHAR(50),ct2Name VARCHAR(50),month1 INTEGER,month2 INTEGER,month3 INTEGER,month4 INTEGER,month5 INTEGER,
month6 INTEGER,month7 INTEGER,month8 INTEGER,month9 INTEGER,month10 INTEGER,month11 INTEGER,month12 INTEGER,heji INTEGER);
lable_exit: BEGIN
SET @SqlCmd = ' INSERT INTO tmp_result (pid,pname,cid,cname,ct1id,ct1name,ct2id,ct2name)
SELECT pid,pname,cid,cname,ct1id,ct1name,ct2id,ct2name FROM
(SELECT ia.pid,a.name AS pname,ia.cid,b.name AS cname,ia.ct1id,c.name AS ct1name,ia.ct2id,d.name AS ct2name
FROM ireport ia
LEFT JOIN province a ON ia.pid=a.id
LEFT JOIN city b ON ia.cid=b.id
LEFT JOIN t_category1 c ON ia.ct1id=c.id
LEFT JOIN t_category2 d ON ia.ct2id=d.id
WHERE YEAR(ia.creatTime)=YEAR(?) ';
IF p_ids IS NOT NULL AND p_ids '' THEN
SET @SqlCmd = CONCAT(@SqlCmd , 'and ia.pid in (');
SET @SqlCmd = CONCAT(@SqlCmd, p_ids);
SET @SqlCmd = CONCAT(@SqlCmd, ')');
END IF;
IF c_ids IS NOT NULL AND c_ids '' THEN
SET @SqlCmd = CONCAT(@SqlCmd , 'and ia.cid in (');
SET @SqlCmd = CONCAT(@SqlCmd, c_ids);
SET @SqlCmd = CONCAT(@SqlCmd, ')');
END IF;
IF ct1_ids IS NOT NULL AND ct1_ids '' THEN
SET @SqlCmd = CONCAT(@SqlCmd , 'and ia.ct1id in (');
SET @SqlCmd = CONCAT(@SqlCmd, ct1_ids);
SET @SqlCmd = CONCAT(@SqlCmd, ')');
END IF;
IF ct2_ids IS NOT NULL AND ct2_ids '' THEN
SET @SqlCmd = CONCAT(@SqlCmd , 'and ia.ct2id in (');
SET @SqlCmd = CONCAT(@SqlCmd, ct2_ids);
SET @SqlCmd = CONCAT(@SqlCmd, ')');
END IF;
SET @SqlCmd = CONCAT(@SqlCmd , ') AS ir GROUP BY pid,pname,cid,cname,ct1id,ct1name,ct2id,ct2name;');
PREPARE stmt1 FROM @SqlCmd;
SET @a = daytime;
EXECUTE stmt1 USING @a;
DEALLOCATE PREPARE stmt1;
LEAVE lable_exit;
END lable_exit;
WHILE i lable_exit: BEGIN
SET @SqlCmd = 'UPDATE tmp_result AS a,
(
SELECT pid,cid,ct1id,ct2id,SUM(crawlerNumber) AS crawlerNumber FROM
(SELECT pid,cid,ct1id,ct2id,crawlerNumber FROM ireport WHERE MONTH(creatTime)=? AND YEAR(creatTime)=YEAR(?)) AS ir
GROUP BY pid,cid,ct1id,ct2id
) AS b
SET a.month';
SET @SqlCmd = CONCAT(@SqlCmd , i);
SET @SqlCmd = CONCAT(@SqlCmd , '=b.crawlerNumber ');
SET @SqlCmd = CONCAT(@SqlCmd , 'WHERE a.pid=b.pid AND a.cid=b.cid AND a.ct1id=b.ct1id AND a.ct2id=b.ct2id; ');
PREPARE stmt1 FROM @SqlCmd;
SET @a = i;
SET @b = daytime;
EXECUTE stmt1 USING @a,@b;
DEALLOCATE PREPARE stmt1;
LEAVE lable_exit;
END lable_exit;
lable_exit: BEGIN
SET @SqlCmd = 'UPDATE tmp_result SET month';
SET @SqlCmd = CONCAT(@SqlCmd , i);
SET @SqlCmd = CONCAT(@SqlCmd , ' = 0 WHERE month');
SET @SqlCmd = CONCAT(@SqlCmd , i);
SET @SqlCmd = CONCAT(@SqlCmd , ' IS NULL');
PREPARE stmt1 FROM @SqlCmd;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
LEAVE lable_exit;
END lable_exit;
SET i = i + 1;
END WHILE;
UPDATE tmp_result SET heji=month1+month2+month3+month4+month5+month6+month7+month8+month9+month10+month11+month12;
INSERT INTO tmp_result (pid,pname,cid,cname,ct1id,ct1name,ct2id,ct2name,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12,heji)
SELECT '' AS pid,'--' AS pname,'' AS cid,'总合计:' AS cname,'' AS ct1id,'--' AS ct1name,'' AS ct2id,'--' AS ct2name,SUM(month1) AS month1,SUM(month2) AS month2,SUM(month3) AS month3,SUM(month4) AS month4,
SUM(month5) AS month5,SUM(month6) AS month6,SUM(month7) AS month7,SUM(month8) AS month8,SUM(month9) AS month9,SUM(month10) AS month10,SUM(month11) AS month11,SUM(month12) AS month12,SUM(heji) AS heji
FROM tmp_result;
IF ireport_chart = 'report' OR ireport_chart = 'REPORT' THEN
SELECT pid,pName,cid,cName,ct1id,ct1Name,ct2id,ct2Name,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12,heji FROM tmp_result;
END IF;
IF ireport_chart = 'chart' OR ireport_chart = 'CHART' THEN
SELECT '' AS pid,pName,'' AS cid,'' AS cName,'' AS ct1id,'' AS ct1Name,'' AS ct2id,'' AS ct2Name,SUM(month1) as month1,SUM(month2) as month2,SUM(month3) as month3,SUM(month4) as month4,
SUM(month5) as month5,SUM(month6) as month6,SUM(month7) as month7,SUM(month8) as month8,SUM(month9) as month9,SUM(month10) as month10,SUM(month11) as month11,SUM(month12) as month12,'' AS heji
FROM (SELECT pid,pName,cid,cName,ct1id,ct1Name,ct2id,ct2Name,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12 FROM tmp_result WHERE pname '--') AS ir
GROUP BY pid;
END IF;
END IF;
IF qureyType = 'week' OR qureyType = 'WEEK' THEN
IF EXISTS(SELECT * FROM information_schema.`TABLES` T WHERE TABLE_NAME = 'tmp_result' AND TABLE_SCHEMA = ku) THEN
DROP TABLE tmp_result;
END IF;
CREATE TABLE tmp_result
(pid VARCHAR(50),pName VARCHAR(50),cid VARCHAR(50),cName VARCHAR(50),ct1id VARCHAR(50),ct1Name VARCHAR(50),ct2id VARCHAR(50),ct2Name VARCHAR(50),week1 INTEGER,week2 INTEGER,week3 INTEGER,week4 INTEGER,heji INTEGER);
lable_exit: BEGIN
SET @SqlCmd = ' INSERT INTO tmp_result (pid,pname,cid,cname,ct1id,ct1name,ct2id,ct2name)
SELECT pid,pname,cid,cname,ct1id,ct1name,ct2id,ct2name FROM
(SELECT ia.pid,a.name AS pname,ia.cid,b.name AS cname,ia.ct1id,c.name AS ct1name,ia.ct2id,d.name AS ct2name
FROM ireport ia
LEFT JOIN province a ON ia.pid=a.id
LEFT JOIN city b ON ia.cid=b.id
LEFT JOIN t_category1 c ON ia.ct1id=c.id
LEFT JOIN t_category2 d ON ia.ct2id=d.id
WHERE YEAR(ia.creatTime)=YEAR(?) And MONTH(ia.creatTime)=MONTH(?) ';
IF p_ids IS NOT NULL AND p_ids '' THEN
SET @SqlCmd = CONCAT(@SqlCmd , 'and ia.pid in (');
SET @SqlCmd = CONCAT(@SqlCmd, p_ids);
SET @SqlCmd = CONCAT(@SqlCmd, ')');
END IF;
IF c_ids IS NOT NULL AND c_ids '' THEN
SET @SqlCmd = CONCAT(@SqlCmd , 'and ia.cid in (');
SET @SqlCmd = CONCAT(@SqlCmd, c_ids);
SET @SqlCmd = CONCAT(@SqlCmd, ')');
END IF;
IF ct1_ids IS NOT NULL AND ct1_ids '' THEN
SET @SqlCmd = CONCAT(@SqlCmd , 'and ia.ct1id in (');
SET @SqlCmd = CONCAT(@SqlCmd, ct1_ids);
SET @SqlCmd = CONCAT(@SqlCmd, ')');
END IF;
IF ct2_ids IS NOT NULL AND ct2_ids '' THEN
SET @SqlCmd = CONCAT(@SqlCmd , 'and ia.ct2id in (');
SET @SqlCmd = CONCAT(@SqlCmd, ct2_ids);
SET @SqlCmd = CONCAT(@SqlCmd, ')');
END IF;
SET @SqlCmd = CONCAT(@SqlCmd , ') AS ir GROUP BY pid,pname,cid,cname,ct1id,ct1name,ct2id,ct2name;');
PREPARE stmt1 FROM @SqlCmd;
SET @a = daytime;
EXECUTE stmt1 USING @a,@a;
DEALLOCATE PREPARE stmt1;
LEAVE lable_exit;
END lable_exit;
WHILE i lable_exit: BEGIN
SET @SqlCmd = 'UPDATE tmp_result AS a,
(
SELECT pid,cid,ct1id,ct2id,SUM(crawlerNumber) AS crawlerNumber FROM
(SELECT pid,cid,ct1id,ct2id,crawlerNumber FROM ireport WHERE WEEK=? AND MONTH(creatTime)=MONTH(?)) AS ir
GROUP BY pid,cid,ct1id,ct2id
) AS b
SET a.week';
SET @SqlCmd = CONCAT(@SqlCmd , i);
SET @SqlCmd = CONCAT(@SqlCmd , '=b.crawlerNumber ');
SET @SqlCmd = CONCAT(@SqlCmd , 'WHERE a.pid=b.pid AND a.cid=b.cid AND a.ct1id=b.ct1id AND a.ct2id=b.ct2id; ');
PREPARE stmt1 FROM @SqlCmd;
SET @a = i;
SET @b = daytime;
EXECUTE stmt1 USING @a,@b;
DEALLOCATE PREPARE stmt1;
LEAVE lable_exit;
END lable_exit;
lable_exit: BEGIN
SET @SqlCmd = 'UPDATE tmp_result SET week';
SET @SqlCmd = CONCAT(@SqlCmd , i);
SET @SqlCmd = CONCAT(@SqlCmd , ' = 0 WHERE week');
SET @SqlCmd = CONCAT(@SqlCmd , i);
SET @SqlCmd = CONCAT(@SqlCmd , ' IS NULL');
PREPARE stmt1 FROM @SqlCmd;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
LEAVE lable_exit;
END lable_exit;
SET i = i + 1;
END WHILE;
UPDATE tmp_result SET heji=week1+week2+week3+week4;
INSERT INTO tmp_result (pid,pname,cid,cname,ct1id,ct1name,ct2id,ct2name,week1,week2,week3,week4,heji)
SELECT '' AS pid,'--' AS pname,'' AS cid,'总合计:' AS cname,'' AS ct1id,'--' AS ct1name,'' AS ct2id,'--' AS ct2name, SUM(week1) AS week1,SUM(week2) AS week2,SUM(week3) AS week3,SUM(week4) AS week4,SUM(heji) AS heji
FROM tmp_result;
IF ireport_chart = 'report' OR ireport_chart = 'REPORT' THEN
SELECT pid,pName,cid,cName,ct1id,ct1Name,ct2id,ct2Name,week1,week2,week3,week4,heji FROM tmp_result;
END IF;
IF ireport_chart = 'chart' OR ireport_chart = 'CHART' THEN
SELECT '' as pid,pName,'' as cid,'' as cName,'' as ct1id,'' as ct1Name,'' as ct2id,'' as ct2Name,SUM(week1) AS week1,SUM(week2) AS week2,SUM(week3) AS week3,SUM(week4) AS week4,'' as heji
FROM (SELECT pid,cid,pname,cname,week1,week2,week3,week4 FROM tmp_result WHERE pname '--') AS ir
GROUP BY pid;
END IF;
END IF;
END $$
DELIMITER ;
作者“李丽芬的博客”

MySQL數據庫升級的步驟包括:1.備份數據庫,2.停止當前MySQL服務,3.安裝新版本MySQL,4.啟動新版本MySQL服務,5.恢復數據庫。升級過程需注意兼容性問題,並可使用高級工具如PerconaToolkit進行測試和優化。

MySQL備份策略包括邏輯備份、物理備份、增量備份、基於復制的備份和雲備份。 1.邏輯備份使用mysqldump導出數據庫結構和數據,適合小型數據庫和版本遷移。 2.物理備份通過複製數據文件,速度快且全面,但需數據庫一致性。 3.增量備份利用二進制日誌記錄變化,適用於大型數據庫。 4.基於復制的備份通過從服務器備份,減少對生產系統的影響。 5.雲備份如AmazonRDS提供自動化解決方案,但成本和控制需考慮。選擇策略時應考慮數據庫大小、停機容忍度、恢復時間和恢復點目標。

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

在MySQL中優化數據庫模式設計可通過以下步驟提升性能:1.索引優化:在常用查詢列上創建索引,平衡查詢和插入更新的開銷。 2.表結構優化:通過規範化或反規範化減少數據冗餘,提高訪問效率。 3.數據類型選擇:使用合適的數據類型,如INT替代VARCHAR,減少存儲空間。 4.分區和分錶:對於大數據量,使用分區和分錶分散數據,提升查詢和維護效率。

tooptimizemysqlperformance,lofterTheSeSteps:1)inasemproperIndexingTospeedUpqueries,2)使用ExplaintplaintoAnalyzeandoptimizequeryPerformance,3)ActiveServerConfigurationStersLikeTlikeTlikeTlikeIkeLikeIkeIkeLikeIkeLikeIkeLikeIkeLikeNodb_buffer_pool_sizizeandmax_connections,4)

MySQL函數可用於數據處理和計算。 1.基本用法包括字符串處理、日期計算和數學運算。 2.高級用法涉及結合多個函數實現複雜操作。 3.性能優化需避免在WHERE子句中使用函數,並使用GROUPBY和臨時表。

MySQL批量插入数据的高效方法包括:1.使用INSERTINTO...VALUES语法,2.利用LOADDATAINFILE命令,3.使用事务处理,4.调整批量大小,5.禁用索引,6.使用INSERTIGNORE或INSERT...ONDUPLICATEKEYUPDATE,这些方法能显著提升数据库操作效率。

在MySQL中,添加字段使用ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column,刪除字段使用ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop。添加字段時,需指定位置以優化查詢性能和數據結構;刪除字段前需確認操作不可逆;使用在線DDL、備份數據、測試環境和低負載時間段修改表結構是性能優化和最佳實踐。


熱AI工具

Undresser.AI Undress
人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool
免費脫衣圖片

Clothoff.io
AI脫衣器

Video Face Swap
使用我們完全免費的人工智慧換臉工具,輕鬆在任何影片中換臉!

熱門文章

熱工具

MantisBT
Mantis是一個易於部署的基於Web的缺陷追蹤工具,用於幫助產品缺陷追蹤。它需要PHP、MySQL和一個Web伺服器。請查看我們的演示和託管服務。

EditPlus 中文破解版
體積小,語法高亮,不支援程式碼提示功能

SublimeText3 英文版
推薦:為Win版本,支援程式碼提示!

SublimeText3 Linux新版
SublimeText3 Linux最新版

記事本++7.3.1
好用且免費的程式碼編輯器