mysql 常用sql

select 
        @name:= underlineToCamel(t.COLUMN_NAME) '列名',
        @type := case 
            when t.DATA_TYPE in ('varchar','char','text','longtext','mediumtext','mediumblob') then 'String'
            when t.DATA_TYPE in ('int','tinyint','integer','smallint') then 'Integer'
            when t.DATA_TYPE in ('bigint') then 'Long'
            when t.DATA_TYPE in ('bit') then 'Boolean'
            when t.DATA_TYPE in ('date','datetime','timestamp') then 'Date'
            when t.DATA_TYPE in ('double','decimal') then 'BigDecimal'
        else '' end as 'java类型',
				@type2 := case 
            when @type = 'String' then 'VARCHAR'
            when @type = 'Integer' then 'INTEGER'
            when @type = 'Date' then 'TIMESTAMP'
            when @type = 'BigDecimal' then 'DECIMAL'
            else ''
        end as 'mybatis类型',
				@type3 := case 
            when @type = 'String' then 'StringValue'
            when @type = 'Integer' then 'Int32Value'
            when @type = 'Date' then 'Timestamp'
            when @type = 'BigDecimal' then 'DoubleValue'
            else ''
        end as 'proto类型',
				@NotBlankMsg := case 
            when @type in ('Integer','Long') then CONCAT('@NotNull(message = "', t.COLUMN_COMMENT, '不能为空")')
            else  CONCAT('@NotBlank(message = "', t.COLUMN_COMMENT, '不能为空")')
        end as 'NotBlankMsg',

        @memo:= t.COLUMN_COMMENT '列描述',
        CONCAT('/** ', t.COLUMN_COMMENT ,' */ private ', @type, ' ', underlineToCamel(t.COLUMN_NAME), '; ' ) as 'java类',
				CONCAT('/** ', t.COLUMN_COMMENT ,' */ ',  @NotBlankMsg , 'private ', @type, ' ', underlineToCamel(t.COLUMN_NAME), '; ' ) as 'java类NotNull',	

        CASE 
            WHEN t.COLUMN_KEY = 'PRI' THEN CONCAT('<id column="', t.COLUMN_NAME ,'" property="', @name, '" jdbcType="', @type2, '" />' )
            ELSE CONCAT('<result column="', t.COLUMN_NAME ,'" property="', @name, '" jdbcType="', @type2, '" />' )
        END as 'BaseResultMap',
        CONCAT(t.COLUMN_NAME, " AS ", @name, ",") as 'SELECT',
        CONCAT('#{', @name, '},') AS 'VALUES',
        CONCAT( t.COLUMN_NAME ,' = #{', @name, '},')    as 'SET',
        CONCAT( '<if test="', @name, ' !=null and ', @name, " !=''", '" >  \n        and ', t.COLUMN_NAME,' = #{',@name,'}\n</if>') AS 'IF SET',

        CONCAT('google.protobuf.', @type3, ' ', @name, ' = ', (@i:=@i+1), ';') as 'proto',
        @len:= REPLACE(REPLACE(SUBSTR(t.COLUMN_TYPE, LOCATE('(', t.COLUMN_TYPE)),'(', ''), ')', '') AS '长度',
        @nullflg := if(t.IS_NULLABLE = 'YES', 'N', 'Y') as '是否为NUll',
        CONCAT('| ',@name, 
                ' | ',  @memo,
                ' | ',  @type,
                ' | ',  @nullflg,
                ' | ',  @len,
                ' | |' ) 'api文档'

from information_schema.columns t
INNER JOIN (SELECT @i:=0) seq
where 1=1
and t.TABLE_SCHEMA = 'sur_train'
and table_name = 'sur_ai_scene';