求MySQL与PostgreSQL中information_schema.columns.data_type取值列表及生成方法

Hey there! Great question—let’s break this down for you, covering both MySQL and PostgreSQL, including how to pull the full list of data_type values yourself and the key differences between the two databases.

Generate the Full List via SQL #

The easiest way to get all possible data_type values in MySQL is to query the information_schema.columns table directly. Run this command:

SELECT DISTINCT data_type FROM information_schema.columns;

If you want to exclude system databases (like information_schema or mysql) to focus on user-defined types, you can filter them out:

SELECT DISTINCT data_type 
FROM information_schema.columns 
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');

Common data_type Values in MySQL #

Here’s a curated list of typical values you’ll see:

• CHAR
• VARCHAR
• TEXT / TINYTEXT / MEDIUMTEXT / LONGTEXT
• INT / TINYINT / SMALLINT / MEDIUMINT / BIGINT
• FLOAT / DOUBLE / DECIMAL
• DATE / DATETIME / TIMESTAMP / TIME / YEAR
• ENUM / SET
• BINARY / VARBINARY
• BLOB / TINYBLOB / MEDIUMBLOB / LONGBLOB
• JSON

Generate the Full List via SQL #

Just like MySQL, you can query information_schema.columns to get all unique data_type values:

SELECT DISTINCT data_type FROM information_schema.columns;

Note: PostgreSQL uses SQL-standard type names, so even if you use shorthand (like varchar instead of character varying) when creating columns, the data_type field will return the standard name.

Common data_type Values in PostgreSQL #

Here’s what you’ll typically encounter:

• character (equivalent to MySQL’s CHAR)
• character varying (equivalent to MySQL’s VARCHAR)
• text
• integer / smallint / bigint
• numeric / real / double precision
• date / timestamp without time zone / timestamp with time zone
• time without time zone / time with time zone / interval
• boolean
• json / jsonb
• bytea (equivalent to MySQL’s BLOB types)
• uuid / inet / cidr / macaddr
• array
• enum
• range

• Naming Style: MySQL uses concise, shorthand type names (e.g., VARCHAR, INT), while PostgreSQL leans into SQL-standard verbose names (e.g., character varying, integer). PostgreSQL does accept shorthand aliases in DDL, but data_type will always show the standard name.
• String/Text Types: MySQL splits large text into multiple tiered types (TINYTEXT, MEDIUMTEXT, etc.) based on storage limits. PostgreSQL uses a single text type for all unbounded text (you can add length constraints, but data_type still remains text).
• Numeric Types: MySQL includes MEDIUMINT and YEAR (a specialized 1-byte numeric type), which PostgreSQL doesn’t have. PostgreSQL uses numeric for arbitrary-precision decimals (similar to MySQL’s DECIMAL) and real for single-precision floats (equivalent to MySQL’s FLOAT).
• Date/Time Features: PostgreSQL natively supports timezone-aware timestamp/time types (timestamp with time zone, time with time zone) and interval types for duration values. MySQL’s TIMESTAMP converts to UTC but isn’t a true timezone-aware type, and it lacks a dedicated interval type.
• Specialized Types: PostgreSQL has built-in support for uuid, inet (IP addresses), jsonb (binary-optimized JSON), and range types—none of these are native in MySQL (you’d need to use workarounds like string storage or extensions). MySQL’s SET type is unique to it; PostgreSQL’s enum requires explicit creation via CREATE TYPE (vs. MySQL’s inline ENUM definition).
• Arrays: PostgreSQL lists ARRAY as a distinct data_type for array columns. MySQL doesn’t have a native array type, so you’d use JSON or comma-separated strings (resulting in json or varchar in data_type).

内容的提问来源于stack exchange，提问作者Damien Golding