sql生成表模型字段【我】

参数都是数据库名

查询列：

SELECT
    b.TABLE_COMMENT as '表名',
--         a.TABLE_SCHEMA as tableSchema,
        a.TABLE_NAME as tableName,
        a.COLUMN_NAME as columnName,
        a.COLUMN_DEFAULT as columnDefault,
        a.IS_NULLABLE as isNullable,
        a.COLUMN_TYPE as columnType,
        a.COLUMN_COMMENT as columnComment,
        a.COLUMN_KEY as columnKey
--         a.EXTRA as extra,
        
        from information_schema.COLUMNS a
        LEFT JOIN information_schema.TABLES b ON a.TABLE_NAME=b.TABLE_NAME
        where a.TABLE_SCHEMA='acctdb'
        ORDER BY b.TABLE_NAME；

 

 

只查询表：

 SELECT * from information_schema.`TABLES` a where a.TABLE_SCHEMA='xxl_job';

。。。。

其他定制【使用if else或者 case改变部分字段】：

SELECT
    -- b.TABLE_COMMENT as '表名',
    --         a.TABLE_SCHEMA as tableSchema,
    a.TABLE_NAME AS tableName,
    a.COLUMN_NAME AS columnName,
     '1' sort,
    -- a.COLUMN_DEFAULT as columnDefault,
    a.COLUMN_COMMENT AS columnComment,
    a.COLUMN_COMMENT AS notes,
    a.COLUMN_TYPE AS columnType,
    -- a.COLUMN_KEY AS columnKey,
    CASE a.COLUMN_KEY
WHEN 'PRI' THEN
    'Y'
ELSE
    ''
END AS IS_PRIMARY,
 -- a.IS_NULLABLE AS isNullable,
IF (a.IS_NULLABLE = 'NO', 'Y', '') AS IS_NOT_NULL --     a.COLUMN_TYPE as columnType
--         a.EXTRA as extra,
FROM
    information_schema. COLUMNS a
LEFT JOIN information_schema. TABLES b ON a.TABLE_NAME = b.TABLE_NAME
WHERE
    a.TABLE_SCHEMA = 'xxl_job'
ORDER BY
    b.TABLE_NAME;

-- SELECT * from information_schema.`TABLES` a where a.TABLE_SCHEMA='xxl_job';

SELECT
    -- b.TABLE_COMMENT as '表名',
    --         a.TABLE_SCHEMA as tableSchema,
    a.TABLE_NAME AS tableName,
    a.COLUMN_NAME AS columnName,
    '1' sort,
    -- a.COLUMN_DEFAULT as columnDefault,
    a.COLUMN_COMMENT AS columnComment,
    a.COLUMN_COMMENT AS notes,
    a.COLUMN_TYPE AS columnType,
    -- a.COLUMN_KEY AS columnKey,
    CASE a.COLUMN_KEY
WHEN 'PRI' THEN
    'Y'
ELSE
    ''
END AS IS_PRIMARY,
 -- a.IS_NULLABLE AS isNullable,
IF (a.IS_NULLABLE = 'NO', 'Y', '') AS IS_NOT_NULL --     a.COLUMN_TYPE as columnType
--         a.EXTRA as extra,
FROM
    information_schema. COLUMNS a
LEFT JOIN information_schema. TABLES b ON a.TABLE_NAME = b.TABLE_NAME
WHERE
    a.TABLE_SCHEMA = 'xxl_job'
ORDER BY
    b.TABLE_NAME;

-- SELECT * from information_schema.`TABLES` a where a.TABLE_SCHEMA='xxl_job';