{"id":235,"date":"2024-06-18T14:34:08","date_gmt":"2024-06-18T06:34:08","guid":{"rendered":"http:\/\/zhaoyanqi.cn\/?p=235"},"modified":"2024-06-18T14:34:09","modified_gmt":"2024-06-18T06:34:09","slug":"mysql%e5%ad%98%e5%82%a8%e8%bf%87%e7%a8%8b","status":"publish","type":"post","link":"https:\/\/zhaoyanqi.cn\/?p=235","title":{"rendered":"mysql\u5b58\u50a8\u8fc7\u7a0b"},"content":{"rendered":"\n<h3 class=\"wp-block-heading\" id=\"86c0a1b1\">\u4e00\u3001\u4ecb\u7ecd<\/h3>\n\n\n\n<p id=\"u10432767\">\u5b58\u50a8\u8fc7\u7a0b\u5305\u542b\u4e86\u4e00\u7cfb\u5217\u53ef\u6267\u884c\u7684SQL\u8bed\u53e5\uff0c\u5b58\u50a8\u8fc7\u7a0b\u5b58\u653e\u4e8eMySQL\u4e2d\uff0c\u901a\u8fc7\u8c03\u7528\u5b83\u7684\u540d\u5b57\u53ef\u4ee5\u6267\u884c\u5176\u5185\u90e8\u7684\u4e00\u5806SQL\u3002<\/p>\n\n\n\n<p id=\"u5321ce59\">\u4f7f\u7528\u5b58\u50a8\u8fc7\u7a0b\u7684\u4f18\u70b9\uff1a<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>\u7528\u4e8e\u66ff\u4ee3\u7a0b\u5e8f\u5199\u7684SQL\u8bed\u53e5\uff0c\u5b9e\u73b0\u7a0b\u5e8f\u4e0eSQL\u89e3\u8026<\/strong><\/li>\n\n\n\n<li><strong>\u57fa\u4e8e\u7f51\u7edc\u4f20\u8f93\uff0c\u4f20\u522b\u540d\u7684\u6570\u636e\u91cf\u5c0f\uff0c\u800c\u76f4\u63a5\u4f20SQL\u6570\u636e\u91cf\u5927<\/strong><\/li>\n<\/ol>\n\n\n\n<p id=\"ub6abc663\">\u4f7f\u7528\u5b58\u50a8\u8fc7\u7a0b\u7684\u7f3a\u70b9\uff1a<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>\u7a0b\u5e8f\u5458\u6269\u5c55\u529f\u80fd\u4e0d\u65b9\u4fbf<\/strong><\/li>\n<\/ol>\n\n\n\n<p id=\"u7194d894\">\u8865\u5145\uff1a\u7a0b\u5e8f\u4e0e\u6570\u636e\u5e93\u7ed3\u5408\u4f7f\u7528\u7684\u4e09\u79cd\u65b9\u5f0f<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>\u65b9\u5f0f\u4e00\uff1a<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>MySQL\uff1a\u5b58\u50a8\u8fc7\u7a0b<\/li>\n\n\n\n<li>\u7a0b\u5e8f\uff1a\u8c03\u7528\u5b58\u50a8\u8fc7\u7a0b<\/li>\n<\/ul>\n\n\n\n<ol class=\"wp-block-list\" start=\"2\">\n<li><strong>\u65b9\u5f0f\u4e8c\uff1a<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>MySQL\uff1a<\/li>\n\n\n\n<li>\u7a0b\u5e8f\uff1a\u7eafSQL\u8bed\u53e5<\/li>\n<\/ul>\n\n\n\n<ol class=\"wp-block-list\" start=\"3\">\n<li><strong>\u65b9\u5f0f\u4e09\uff1a<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>MySQL:<\/li>\n\n\n\n<li>\u7a0b\u5e8f\uff1a\u7c7b\u548c\u5bf9\u8c61\uff0c\u5373ORM\uff08\u672c\u8d28\u8fd8\u662f\u7eafSQL\u8bed\u53e5\uff09<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"20c1c2e2\">\u4e8c\u3001\u521b\u5efa\u7b80\u5355\u5b58\u50a8\u8fc7\u7a0b\uff08\u65e0\u53c2\uff09<\/h3>\n\n\n\n<pre id=\"829f67eb\" class=\"wp-block-code\"><code>delimiter \/\/\nCREATE PROCEDURE p1()\nBEGIN\n    SELECT * FROM blog;\n    INSERT INTO blog(name, sub_time) VALUES(\"xxx\", NOW());\nEND \/\/\ndelimiter ;<\/code><\/pre>\n\n\n\n<p id=\"u3e809ae6\">\u5728MySQL\u4e2d\u8c03\u7528\uff1a<\/p>\n\n\n\n<pre id=\"569e5b08\" class=\"wp-block-code\"><code>CALL p1();<\/code><\/pre>\n\n\n\n<p id=\"ud38ab6e6\">\u5728Python\u4e2d\u57fa\u4e8epymysql\u8c03\u7528\uff1a<\/p>\n\n\n\n<pre id=\"1ea306f1\" class=\"wp-block-code\"><code>cursor.callproc('p1')\nprint(cursor.fetchall())<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"c27996b4\">\u4e09\u3001\u521b\u5efa\u5b58\u50a8\u8fc7\u7a0b\uff08\u6709\u53c2\uff09<\/h3>\n\n\n\n<p id=\"u171cda4c\">\u5bf9\u4e8e\u5b58\u50a8\u8fc7\u7a0b\uff0c\u53ef\u4ee5\u63a5\u6536\u53c2\u6570\uff0c\u5176\u53c2\u6570\u6709\u4e09\u7c7b\uff1a<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>IN<\/code> \u00a0 \u00a0\u4ec5\u7528\u4e8e\u4f20\u5165\u53c2\u6570\u7528<\/li>\n\n\n\n<li><code>OUT<\/code> \u00a0 \u4ec5\u7528\u4e8e\u8fd4\u56de\u503c\u7528<\/li>\n\n\n\n<li><code>INOUT<\/code> \u65e2\u53ef\u4ee5\u4f20\u5165\u53c8\u53ef\u4ee5\u5f53\u4f5c\u8fd4\u56de\u503c<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"77821b51\">\u793a\u4f8b1\uff1a\u4ec5\u4f20\u5165\u53c2\u6570<\/h4>\n\n\n\n<pre id=\"2b251bd8\" class=\"wp-block-code\"><code>delimiter \/\/\nCREATE PROCEDURE p2(\n    IN n1 INT,\n    IN n2 INT\n)\nBEGIN\n    SELECT * FROM blog WHERE id &gt; n1;\nEND \/\/\ndelimiter ;<\/code><\/pre>\n\n\n\n<p id=\"ufc22c36d\">\u5728MySQL\u4e2d\u8c03\u7528\uff1a<\/p>\n\n\n\n<pre id=\"d743bf49\" class=\"wp-block-code\"><code>CALL p2(3, 2);<\/code><\/pre>\n\n\n\n<p id=\"ud7453353\">\u5728Python\u4e2d\u57fa\u4e8epymysql\u8c03\u7528\uff1a<\/p>\n\n\n\n<pre id=\"55558261\" class=\"wp-block-code\"><code>cursor.callproc('p2', (3, 2))\nprint(cursor.fetchall())<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"5a8d5ce9\">\u793a\u4f8b2\uff1a\u4f20\u5165\u548c\u8fd4\u56de\u53c2\u6570<\/h4>\n\n\n\n<pre id=\"17b17cd4\" class=\"wp-block-code\"><code>delimiter \/\/\nCREATE PROCEDURE p3(\n    IN n1 INT,\n    OUT res INT\n)\nBEGIN\n    SELECT * FROM blog WHERE id &gt; n1;\n    SET res = 1;\nEND \/\/\ndelimiter ;<\/code><\/pre>\n\n\n\n<p id=\"u31c50824\">\u5728MySQL\u4e2d\u8c03\u7528\uff1a<\/p>\n\n\n\n<pre id=\"11670209\" class=\"wp-block-code\"><code>SET @res = 0;\nCALL p3(3, @res);\nSELECT @res;<\/code><\/pre>\n\n\n\n<p id=\"ucb68b307\">\u5728Python\u4e2d\u57fa\u4e8epymysql\u8c03\u7528\uff1a<\/p>\n\n\n\n<pre id=\"00ad1f1c\" class=\"wp-block-code\"><code>cursor.callproc('p3', (3, 0))\nprint(cursor.fetchall())\n\ncursor.execute('SELECT @_p3_0, @_p3_1;')\nprint(cursor.fetchall())<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"9493f317\">\u793a\u4f8b3\uff1a\u65e2\u4f20\u5165\u53c8\u8fd4\u56de\u53c2\u6570<\/h4>\n\n\n\n<pre id=\"34201d58\" class=\"wp-block-code\"><code>delimiter \/\/\nCREATE PROCEDURE p4(\n    INOUT n1 INT\n)\nBEGIN\n    SELECT * FROM blog WHERE id &gt; n1;\n    SET n1 = 1;\nEND \/\/\ndelimiter ;<\/code><\/pre>\n\n\n\n<p id=\"u5490e272\">\u5728MySQL\u4e2d\u8c03\u7528\uff1a<\/p>\n\n\n\n<pre id=\"2e0f323a\" class=\"wp-block-code\"><code>SET @x = 3;\nCALL p4(@x);\nSELECT @x;<\/code><\/pre>\n\n\n\n<p id=\"uce63151b\">\u5728Python\u4e2d\u57fa\u4e8epymysql\u8c03\u7528\uff1a<\/p>\n\n\n\n<pre id=\"824f3e89\" class=\"wp-block-code\"><code>cursor.callproc('p4', (3,))\nprint(cursor.fetchall())\n\ncursor.execute('SELECT @_p4_0;')\nprint(cursor.fetchall())<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"025c0e17\">\u56db\u3001\u4e8b\u52a1\u5904\u7406\u7684\u5b58\u50a8\u8fc7\u7a0b<\/h3>\n\n\n\n<pre id=\"847ed95a\" class=\"wp-block-code\"><code>delimiter \/\/\nCREATE PROCEDURE p5(\n    OUT p_return_code TINYINT\n)\nBEGIN \n    DECLARE EXIT HANDLER FOR SQLEXCEPTION \n    BEGIN \n        SET p_return_code = 1; \n        ROLLBACK; \n    END; \n\n    DECLARE EXIT HANDLER FOR SQLWARNING \n    BEGIN \n        SET p_return_code = 2; \n        ROLLBACK; \n    END; \n\n    START TRANSACTION; \n        DELETE FROM tb1; #\u6267\u884c\u5931\u8d25\n        INSERT INTO blog(name, sub_time) VALUES('yyy', NOW());\n    COMMIT; \n\n    SET p_return_code = 0; # 0\u4ee3\u8868\u6267\u884c\u6210\u529f\n\nEND \/\/\ndelimiter ;<\/code><\/pre>\n\n\n\n<p id=\"u074b548b\">\u5728MySQL\u4e2d\u8c03\u7528\u5b58\u50a8\u8fc7\u7a0b\uff1a<\/p>\n\n\n\n<pre id=\"37118bc4\" class=\"wp-block-code\"><code>SET @res = 123;\nCALL p5(@res);\nSELECT @res;<\/code><\/pre>\n\n\n\n<p id=\"ud04726b2\">\u5728Python\u4e2d\u57fa\u4e8epymysql\u8c03\u7528\u5b58\u50a8\u8fc7\u7a0b\uff1a<\/p>\n\n\n\n<pre id=\"2e24c8ab\" class=\"wp-block-code\"><code>cursor.callproc('p5', (123,))\nprint(cursor.fetchall())\n\ncursor.execute('SELECT @_p5_0;')\nprint(cursor.fetchall())<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"76c5f689\">\u4e94\u3001\u6267\u884c\u5b58\u50a8\u8fc7\u7a0b<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"c5f681b5\">\u65e0\u53c2\u6570<\/h4>\n\n\n\n<pre id=\"463219df\" class=\"wp-block-code\"><code>CALL proc_name();<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"46c42e20\">\u6709\u53c2\u6570\uff0c\u5168<code>IN<\/code><\/h4>\n\n\n\n<pre id=\"82b478a3\" class=\"wp-block-code\"><code>CALL proc_name(1, 2);<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"ff336987\">\u6709\u53c2\u6570\uff0c\u6709<code>IN<\/code>\uff0c<code>OUT<\/code>\uff0c<code>INOUT<\/code><\/h4>\n\n\n\n<pre id=\"0930fe70\" class=\"wp-block-code\"><code>SET @t1 = 0;\nSET @t2 = 3;\nCALL proc_name(1, 2, @t1, @t2);<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"RQOqc\">\u5728Python\u4e2d\u6267\u884c\u5b58\u50a8\u8fc7\u7a0b<\/h4>\n\n\n\n<pre id=\"9b785823\" class=\"wp-block-code\"><code>#!\/usr\/bin\/env python\n# -*- coding:utf-8 -*-\nimport pymysql\n\nconn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')\ncursor = conn.cursor(pymysql.cursors.DictCursor)\n# \u6267\u884c\u5b58\u50a8\u8fc7\u7a0b\ncursor.callproc('p1', args=(1, 22, 3, 4))\n# \u83b7\u53d6\u6267\u884c\u5b8c\u5b58\u50a8\u7684\u53c2\u6570\ncursor.execute(\"SELECT @_p1_0, @_p1_1, @_p1_2, @_p1_3\")\nresult = cursor.fetchall()\n\nconn.commit()\ncursor.close()\nconn.close()\n\nprint(result)<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"5964eabd\">\u516d\u3001\u5220\u9664\u5b58\u50a8\u8fc7\u7a0b<\/h3>\n\n\n\n<pre id=\"cdd7abe8\" class=\"wp-block-code\"><code>DROP PROCEDURE proc_name;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u4e00\u3001\u4ecb\u7ecd \u5b58\u50a8\u8fc7\u7a0b\u5305\u542b\u4e86\u4e00\u7cfb\u5217\u53ef\u6267\u884c\u7684SQL\u8bed\u53e5\uff0c\u5b58\u50a8\u8fc7\u7a0b\u5b58\u653e\u4e8eMySQL\u4e2d\uff0c\u901a\u8fc7\u8c03\u7528\u5b83\u7684\u540d\u5b57\u53ef\u4ee5\u6267\u884c\u5176\u5185\u90e8\u7684 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,12],"tags":[],"class_list":["post-235","post","type-post","status-publish","format-standard","hentry","category-mysql","category-sql"],"_links":{"self":[{"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=\/wp\/v2\/posts\/235","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=235"}],"version-history":[{"count":1,"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=\/wp\/v2\/posts\/235\/revisions"}],"predecessor-version":[{"id":236,"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=\/wp\/v2\/posts\/235\/revisions\/236"}],"wp:attachment":[{"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=235"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=235"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=235"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}