获取mysql8.0自定义函数与存储过程生成文件

最新推荐文章于 2025-05-27 17:39:28 发布

原创最新推荐文章于 2025-05-27 17:39:28 发布 · 2.2k 阅读

0 ·

CC 4.0 BY-SA版权

本文详细介绍了如何在MySQL8.0中导出自定义函数和存储过程为文件，包括必要的步骤和操作，帮助数据库管理员更好地管理和备份自定义数据库组件。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

#!/usr/bin/python
# -*- coding=utf-8 -*-
import pymysql

def fn_get_mysql_v5():
    pass
def fn_get_mysql_v8():
    pass




def fn_proc_ddl2sql(db,is_dbMultiFiles):
    mydb = pymysql.connect("127.0.0.1","root","Root123.","anhua",port=3306,charset='utf8' )
    #mydb.set_character_set('utf8')
    mycursor = mydb.cursor()
    mycursor.execute('SET CHARACTER SET utf8;')
    mycursor.execute('SET NAMES utf8;')
    mycursor.execute('SET character_set_connection=utf8;')
    mycursor.execute("select version()" )
    mysqlVer = mycursor.fetchall()[0][0]
    print mysqlVer
    if mysqlVer>='8.0':
        sqlV58 = '''
                SELECT CONCAT('show create ' ,ROUTINE_TYPE  , '`', ROUTINE_SCHEMA  ,'`.`', p.`SPECIFIC_NAME`,'`;') showSql ,
                       ROUTINE_SCHEMA,
                       ROUTINE_TYPE,
                       SPECIFIC_NAME
                FROM information_schema.`ROUTINES` p
                WHERE 1=1 -- p.`ROUTINE_TYPE` ='PROCEDURE'
                and p.`ROUTINE_SCHEMA` not in ('sys','mysql','performance_schema','information_schema')
                and p.`ROUTINE_SCHEMA` like "'''  + db + '"' + ' limit 10000 '
        #print sql8
    else: # mysqlVer>='5.0':  此处还没有写好，暂时先这样吧
        sqlV58 = '''
                SELECT CONCAT('show create ' ,ROUTINE_TYPE  , '`', ROUTINE_SCHEMA  ,'`.`', p.`SPECIFIC_NAME`,'`;') showSql ,
                       ROUTINE_SCHEMA,
                       ROUTINE_TYPE,
                       SPECIFIC_NAME
                FROM mysql.proc p
                WHERE 1=1 -- p.`ROUTINE_TYPE` ='PROCEDURE'
                and p.`ROUTINE_SCHEMA` not in ('sys','mysql','performance_schema','information_schema','sakila')
                and p.`ROUTINE_SCHEMA` like "'''  + db + '"' + ' limit 10000 '

    mycursor.execute(sqlV58)
    readyGet_sql = mycursor.fetchall()
    #print readyGet_sql
    for Row in readyGet_sql:
        show_create,ROUTINE_SCHEMA,ROUTINE_TYPE,SPECIFIC_NAME  = Row[0], Row[1], Row[2], Row[3]
        mycursor.execute(show_create)
        ddl =  mycursor.fetchall()[0][2]
        proc_ddlSql= 'DELIMITER $$'+ chr(10)+chr(13) + 'use `'+ROUTINE_SCHEMA+'` $$' + chr(10)+chr(13) + ' drop '+ROUTINE_TYPE + ' if exists `'+ SPECIFIC_NAME +'`$$' + chr(10)+chr(13) + ddl+ '$$'  + chr(10)+chr(13)

        print type(proc_ddlSql),proc_ddlSql
        if is_dbMultiFiles == 0:
            path = ('f:/ddl/'+ROUTINE_SCHEMA+'.sql')
        else :
            path = ('f:/ddl/'+ROUTINE_SCHEMA+'.'+ROUTINE_TYPE+'.'+SPECIFIC_NAME+'.sql') #.decode('gbk2312')
        print type(path),path
        f=open(path ,'a')
        f.write(proc_ddlSql.encode('utf-8'))
        f.close()

def fn_mysql_con():
    pass


if __name__ == '__main__':
    is_dbMultiFiles=0  ######################  y调节此处生成单文件或者多文件 ########################
    fn_proc_ddl2sql("%",is_dbMultiFiles)