mysql 创建简单的临时表 tmp

create database test;

use test;

DROP TABLE IF EXISTS `tmp`;

CREATE TABLE `tmp`  (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,

  PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

INSERT INTO `tmp` VALUES (1, 'klvchen');

INSERT INTO `tmp` VALUES (2, 'lily');

INSERT INTO `tmp` VALUES (3, 'lucy');

INSERT INTO `tmp` VALUES (4, 'james');

INSERT INTO `tmp` VALUES (5, 'jim');

mysql 创建存储过程

delimiter $$        # 自定义 mysql 的分隔符

CREATE  PROCEDURE p1(

    in i1 int,      # 仅用于传入参数用

    in i2 int,

    inout i3 int,   # 既可以传入又可以当作返回值

    out r1 int      # 仅用于返回值用，外部传进来的值无用

)

BEGIN

    DECLARE temp1 int;

    DECLARE temp2 int default 0;

    set temp1 = 1;

    set r1 = i1 + i2 + temp1 + temp2;

    set i3 = i3 + 100;

		SELECT * FROM tmp;

end $$

delimiter ;

python 调用 mysql 存储过程

import pymysql

PY_MYSQL_CONN_DICT = {

    "host" : '192.168.0.214',

    "port" : 3306,

    "user" : 'root',

    "passwd" : '123456',

    "db" : 'tmpdb'

}

conn = pymysql.connect(**PY_MYSQL_CONN_DICT)

cusor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 调用 p1 存储过程，传入4个参数

cusor.callproc('p1', args=(1, 2, 3, 4))

# 返回获得的集合，即存储函数中的 SELECT * FROM tmp; 结果

res1 = cusor.fetchall()

print(res1)

# 以 python 固定格式获取返回的值：@_存储过程名_0, 第一个返回值

cusor.execute("select @_p1_0, @_p1_1, @_p1_2, @_p1_3")

res2 = cusor.fetchall()

print(res2)

conn.commit()

cusor.close()

conn.close()