mysql生成model存储过程

DELIMITER $$

USE `数据库`$$

DROP PROCEDURE IF EXISTS `GetCSharpModel`$$

CREATE DEFINER=`root`@`%` PROCEDURE `GetCSharpModel`(IN pDataBaseName VARCHAR(255),IN pTableName VARCHAR(255))
BEGIN
DECLARE vClassName VARCHAR(255);
DECLARE vClassCode MEDIUMTEXT;
DECLARE colComment VARCHAR(1024);
DECLARE colType VARCHAR(1024);
DECLARE fName VARCHAR(1024);
DECLARE fNameNew VARCHAR(1024);
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE code_cursor CURSOR FOR 
            SELECT ColumnComment,ColumnType,FieldName FROM temp1; 
DECLARE CONTINUE HANDLER FOR 
            NOT FOUND SET v_finished = 1;
            -- 生成实体类名
            SELECT REPLACE(GROUP_CONCAT(CONCAT(LOWER(LEFT(A.Field,1)),SUBSTRING(A.Field,2,(LENGTH(A.Field)-1)))),',','')
            
            FROM (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(pTableName,'_',help_topic_id+1),'_',-1) AS FIELD 
                        FROM mysql.help_topic 
            WHERE help_topic_id < LENGTH(pTableName)-LENGTH(REPLACE(pTableName,'_',''))+1) A 
                        
            INTO vClassName ;
      -- 创建临时表
    CREATE TEMPORARY TABLE IF NOT EXISTS  temp1 ENGINE=MYISAM  
    AS (
                    SELECT  COLUMN_NAME  AS FieldName, COLUMN_COMMENT AS ColumnComment,
                        CASE  
                            WHEN DATA_TYPE='bigint' AND IS_NULLABLE = 'YES'  THEN 'long?'
                            WHEN DATA_TYPE='bit' AND IS_NULLABLE = 'YES'  THEN 'bool?'
                            WHEN DATA_TYPE='char' AND COLUMN_TYPE = 'char(36)' AND IS_NULLABLE = 'YES' THEN 'Guid?'
                            WHEN DATA_TYPE='date' AND IS_NULLABLE = 'YES'  THEN 'DateTime?'
                            WHEN DATA_TYPE='datetime' AND IS_NULLABLE = 'YES' THEN 'DateTime?'
                            WHEN DATA_TYPE='decimal' AND IS_NULLABLE = 'YES' THEN 'decimal?'
                            WHEN DATA_TYPE='float' AND IS_NULLABLE = 'YES' THEN 'float?'
                            WHEN DATA_TYPE='int' AND IS_NULLABLE = 'YES' THEN 'int?'
                            WHEN DATA_TYPE='numeric' AND IS_NULLABLE = 'YES' THEN 'decimal?'
                            WHEN DATA_TYPE='decimal' AND IS_NULLABLE = 'YES' THEN 'decimal?'
                            WHEN DATA_TYPE='double' AND IS_NULLABLE = 'YES' THEN 'double?'
                            WHEN DATA_TYPE='real' AND IS_NULLABLE = 'YES' THEN 'double?'
                            WHEN DATA_TYPE='smallint' AND IS_NULLABLE = 'YES' THEN 'short?'
                            WHEN DATA_TYPE='mediumint' AND IS_NULLABLE = 'YES' THEN 'int?'
                            WHEN DATA_TYPE='time'  AND IS_NULLABLE = 'YES' THEN 'TimeSpan?'
                            WHEN DATA_TYPE='timestamp' AND IS_NULLABLE = 'YES' THEN 'DateTime?'
                            WHEN DATA_TYPE='tinyint' AND COLUMN_TYPE = 'tinyint(1)' AND IS_NULLABLE = 'YES' THEN 'bool?'
                            WHEN DATA_TYPE='tinyint' AND IS_NULLABLE = 'YES' THEN 'byte?'
                            WHEN DATA_TYPE='year'  AND IS_NULLABLE = 'YES' THEN 'uint?'
                            
                            WHEN DATA_TYPE='bigint' THEN 'long'
                            WHEN DATA_TYPE='binary' THEN 'byte[]'
                            WHEN DATA_TYPE='bit' THEN 'bool'                
                            WHEN DATA_TYPE='char' AND COLUMN_TYPE = 'char(36)' THEN 'Guid'                    
                            WHEN DATA_TYPE='date' THEN 'DateTime'                
                            WHEN DATA_TYPE='datetime' THEN 'DateTime'    
                            WHEN DATA_TYPE='decimal' THEN 'decimal'    
                            WHEN DATA_TYPE='float' THEN 'float'
                            WHEN DATA_TYPE='image' THEN 'byte[]'
                            WHEN DATA_TYPE='int' THEN 'int'
                            WHEN DATA_TYPE='numeric' THEN 'decimal'
                            WHEN DATA_TYPE='decimal' THEN 'decimal'
                            WHEN DATA_TYPE='double' THEN 'double'
                            WHEN DATA_TYPE='real' THEN 'double'
                            WHEN DATA_TYPE='smallint' THEN 'short'
                            WHEN DATA_TYPE='mediumint' THEN 'int'
                            WHEN DATA_TYPE='text' THEN 'string'
                            WHEN DATA_TYPE='mediumtext' THEN 'string'
                            WHEN DATA_TYPE='longtext' THEN 'string'                        
                            WHEN DATA_TYPE='time' THEN 'TimeSpan'                        
                            WHEN DATA_TYPE='timestamp' THEN 'DateTime'                        
                            WHEN DATA_TYPE='tinyint' AND COLUMN_TYPE = 'tinyint(1)'  THEN 'bool'
                            WHEN DATA_TYPE='tinyint' THEN 'byte'
                            WHEN DATA_TYPE='varbinary' THEN 'byte[]'
                            WHEN DATA_TYPE='varchar' THEN 'string'
                            WHEN DATA_TYPE='year' THEN 'uint'
                            ELSE 'UNKNOWN_' + DATA_TYPE
                        END AS ColumnType
                        FROM ( SELECT  COLUMN_NAME, DATA_TYPE, COLUMN_TYPE,COLUMN_COMMENT,IS_NULLABLE  FROM INFORMATION_SCHEMA.COLUMNS  WHERE table_schema=pDataBaseName AND table_name = pTableName) A);
    SET vClassCode = '';
        
        -- 打开游标
    OPEN code_cursor;
            get_code: LOOP
                FETCH code_cursor INTO  colComment,colType,fName;
                IF v_finished = 1 THEN
                    LEAVE get_code;
                END IF;
                                
                                -- 处理字段
                                SELECT REPLACE(GROUP_CONCAT(CONCAT(LOWER(LEFT(A.Field,1)),SUBSTRING(A.Field,2,(LENGTH(A.Field)-1)))),',','')
                                FROM 
                                ( SELECT DISTINCT fName AS FIELD 
                                        FROM mysql.help_topic 
                                        WHERE help_topic_id < LENGTH(fName)-LENGTH(REPLACE(fName,'_',''))+1 ) A INTO fNameNew;
                -- 组合代码字符串
                SELECT  CONCAT(vClassCode,'\r\n\r\n', CONCAT( '/// <summary>\r\n','/// ',colComment,'\r\n/// </summary>\r\n', 'public ', colType , ' ' ,fNameNew,' { get; set; }')) 
                                INTO  vClassCode ;
            END LOOP get_code;
        CLOSE code_cursor;
        -- 删除临时表
        DROP TABLE temp1;
    
        -- 最终生成类
        SELECT CONCAT('public class ','m_',pTableName,'\r\n{', vClassCode,'\r\n}') AS ModelClass;
    END$$

DELIMITER ;

使用 call GetCSharpModel('数据库',‘表名’)