写一个sql 汇总查询 字段长度，支持 decimal ，bigint ，varchar 字段

<think>我们想要查询MySQL中decimal、bigint和varchar类型的字段的长度信息。 对于decimal类型，我们需要获取其精度和标度（即总位数和小数位数），例如DECIMAL(10,2)表示总位数10，小数位数2。 对于bigint类型，它没有小数位数，但我们可以显示其有符号或无符号的范围，但这里用户要求的是“字段长度”，通常bigint固定占用8字节，但我们可以显示其数据类型（如BIGINT(20)中的20，这个20是显示宽度，并不影响存储）。 对于varchar类型，我们需要获取其最大字符长度（即括号内的数字）。 此外，我们还需要考虑这些字段所在的表名、字段名等信息。 我们可以通过查询information_schema数据库的COLUMNS表来获取这些信息。 具体需要查询的列： - TABLE_SCHEMA: 数据库名 - TABLE_NAME: 表名 - COLUMN_NAME: 字段名 - DATA_TYPE: 数据类型（如decimal, bigint, varchar等） - 对于decimal类型，我们需要NUMERIC_PRECISION（总位数）和NUMERIC_SCALE（小数位数） - 对于varchar类型，我们需要CHARACTER_MAXIMUM_LENGTH（最大字符长度） - 对于bigint类型，我们可以获取NUMERIC_PRECISION（虽然bigint不是小数，但这里会显示近似的位数，例如bigint有符号的数值范围是-2^63到2^63-1，所以精度大约是19位，无符号大约是20位）和CHARACTER_OCTET_LENGTH（但bigint是数值类型，这个值为空）。另外，我们也可以获取COLUMN_TYPE，它包含了完整的类型定义（如BIGINT(20)）。 但是，用户要求的是“字段长度”，对于bigint，我们通常关心的是它的显示宽度（即括号中的数字），这个可以通过COLUMN_TYPE解析出来。但是，在COLUMNS表中，有一个COLUMN_TYPE字段，它包含了完整的类型定义（如decimal(10,2)、bigint(20)等）。另外，还有一个CHARACTER_MAXIMUM_LENGTH，这个只对字符类型有效。 因此，我们可以这样设计查询： 1. 选择我们关心的字段：数据库名、表名、字段名、数据类型、列类型（COLUMN_TYPE）以及针对不同类型提取的长度信息。 2. 我们可以使用CASE语句，根据数据类型来显示不同的长度信息。 但是，由于decimal和bigint都是数值类型，而varchar是字符类型，我们可以这样展示： - 对于decimal：显示为“总位数,小数位数”（例如：10,2） - 对于bigint：显示其显示宽度（即括号中的数字，如果没有括号则显示为默认的，比如bigint默认是20，但实际存储是固定的8字节） - 对于varchar：显示其最大字符长度 注意：在COLUMNS表中，对于数值类型，我们可以使用NUMERIC_PRECISION和NUMERIC_SCALE来获取decimal的精度和标度。对于bigint，NUMERIC_PRECISION会返回一个数值（例如19或20），但我们也可以从COLUMN_TYPE中提取显示宽度（如果有的话）。不过，显示宽度在bigint中并不是一个重要的属性，因为它只影响显示（如ZEROFILL时用），但用户要求的是“字段长度”，所以我们可以将显示宽度作为bigint的“长度”。 另外，对于varchar，我们使用CHARACTER_MAXIMUM_LENGTH。 因此，我们可以这样写查询： 选择： TABLE_SCHEMA AS `Database`, TABLE_NAME AS `Table`, COLUMN_NAME AS `Column`, DATA_TYPE AS `Type`, CASE DATA_TYPE WHEN 'decimal' THEN CONCAT(NUMERIC_PRECISION, ',', NUMERIC_SCALE) WHEN 'bigint' THEN IF(COLUMN_TYPE LIKE 'bigint(%)', REPLACE(REPLACE(SUBSTRING_INDEX(COLUMN_TYPE, '(', -1), ')', ''), ' ', ''), '') -- 注意：bigint如果没有显示指定，COLUMN_TYPE就是'bigint'，没有括号，所以我们可以提取括号内的数字，如果没有则显示默认的显示宽度？但是实际上，在COLUMNS表中，即使没有指定，COLUMN_TYPE也会显示为'bigint(20)'（如果显示宽度是20的话）。所以我们可以直接提取括号内的数字。 WHEN 'varchar' THEN CAST(CHARACTER_MAXIMUM_LENGTH AS CHAR) ELSE NULL END AS `Length` 但是，上面的CASE中，对于bigint我们提取了括号内的内容，但注意，bigint的COLUMN_TYPE可能是'bigint'、'bigint(20)'、'bigint unsigned'等。所以我们需要更谨慎地处理。 另一种方法是：我们直接使用COLUMN_TYPE，然后根据数据类型提取长度信息。但是这样比较麻烦。 实际上，对于bigint，我们更关心的是它的存储范围（即8字节），但用户要求的是“字段长度”，在MySQL中，对于整数类型，括号内的数字是显示宽度。所以我们可以这样： 我们统一使用： - decimal: 显示精度和标度 - bigint: 显示显示宽度（通过COLUMN_TYPE中的括号内数字，如果没有则显示默认值，如20） - varchar: 显示最大字符长度 但是，在COLUMNS表中，bigint类型在COLUMN_TYPE字段中通常都会包含显示宽度（即使建表时没有指定，默认也会加上）。例如，如果建表时写`col bigint`，那么COLUMN_TYPE的值就是'bigint(20)'。所以我们可以提取括号内的数字。 提取方法：我们可以使用SUBSTRING_INDEX函数来提取括号内的部分。 但是，如果字段是unsigned，COLUMN_TYPE可能是'bigint(20) unsigned'，所以括号内的内容在第一个括号内。 因此，我们可以这样提取： SUBSTRING(COLUMN_TYPE, LOCATE('(', COLUMN_TYPE)+1, LOCATE(')', COLUMN_TYPE) - LOCATE('(', COLUMN_TYPE) - 1) 但是，如果COLUMN_TYPE中没有括号，则返回空字符串。对于bigint类型，通常都有括号，所以我们可以这样写： CASE WHEN DATA_TYPE = 'bigint' AND COLUMN_TYPE LIKE '%(%' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(COLUMN_TYPE, '(', -1), ')', 1) WHEN DATA_TYPE = 'bigint' THEN '' -- 实际上这种情况不会出现，因为bigint的COLUMN_TYPE一定包含括号 ELSE ... END 但是，为了简化，我们可以这样写： IF(COLUMN_TYPE LIKE '%(%', SUBSTRING(COLUMN_TYPE, LOCATE('(', COLUMN_TYPE)+1, LOCATE(')', COLUMN_TYPE) - LOCATE('(', COLUMN_TYPE) - 1), NULL) -- 注意：这个提取方式对于有多个括号的情况可能不准确，但一般类型定义只有一个括号。 不过，更简单的方式是使用SUBSTRING_INDEX： SUBSTRING_INDEX(SUBSTRING_INDEX(COLUMN_TYPE, '(', -1), ')', 1) 这个的意思是：先取第一个'('之后的子串（从右边数第一个'('的右边部分），然后再从这个子串中取第一个')'左边的部分。 但是，如果COLUMN_TYPE中没有括号，那么第一个SUBSTRING_INDEX会返回整个字符串，然后第二个SUBSTRING_INDEX会返回整个字符串（因为没有')'），所以我们需要判断是否有括号。 因此，我们可以在CASE中这样写： CASE DATA_TYPE WHEN 'decimal' THEN CONCAT(COALESCE(NUMERIC_PRECISION, ''), ',', COALESCE(NUMERIC_SCALE, '')) WHEN 'bigint' THEN IF(COLUMN_TYPE LIKE '%(%', SUBSTRING_INDEX(SUBSTRING_INDEX(COLUMN_TYPE, '(', -1), ')', 1), '') -- 如果bigint没有括号，则返回空字符串（但实际上不会出现） WHEN 'varchar' THEN CAST(CHARACTER_MAXIMUM_LENGTH AS CHAR) ELSE NULL END 但是，对于decimal类型，我们也可以从COLUMN_TYPE中提取，但COLUMNS表已经提供了NUMERIC_PRECISION和NUMERIC_SCALE，所以可以直接使用。 另外，我们还需要过滤只显示这三种类型。 最终SQL查询如下：</think>要查询 MySQL 中 `decimal`、`bigint` 和 `varchar` 类型字段的长度信息，可以使用以下 SQL 查询语句。该查询会从 `information_schema.COLUMNS` 系统表中提取元数据： ```sql SELECT TABLE_SCHEMA AS `数据库名`, TABLE_NAME AS `表名`, COLUMN_NAME AS `字段名`, DATA_TYPE AS `数据类型`, CASE DATA_TYPE WHEN 'decimal' THEN CONCAT(NUMERIC_PRECISION, ',', NUMERIC_SCALE) WHEN 'bigint' THEN CAST(NUMERIC_PRECISION AS CHAR) WHEN 'varchar' THEN CAST(CHARACTER_MAXIMUM_LENGTH AS CHAR) ELSE NULL END AS `长度/精度`, COLUMN_TYPE AS `完整类型定义`, CHARACTER_SET_NAME AS `字符集`, IS_NULLABLE AS `是否可空` FROM information_schema.COLUMNS WHERE DATA_TYPE IN ('decimal', 'bigint', 'varchar') AND TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema') -- 排除系统库 ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION; ``` ### 查询结果说明： 1. **decimal 类型** `长度/精度` 列显示格式为 `总位数,小数位数`（如 `10,2` 表示共10位，其中小数占2位） 2. **bigint 类型** `长度/精度` 列显示数值精度（固定为19位，对应8字节存储） 3. **varchar 类型** `长度/精度` 列显示最大字符长度（如 `255`） 4. **其他关键列**： - `完整类型定义`：显示建表时的完整定义（如 `decimal(10,2) UNSIGNED`） - `字符集`：仅对 varchar 有效（如 `utf8mb4`） - `是否可空`：标识字段是否允许 NULL 值 ### 重要注意事项： 1. **varchar 的实际存储限制** VARCHAR 的最大有效长度受字符集影响。例如： - `utf8mb4` 字符集（4字节/字符）：最大长度为 $ \lfloor \frac{65535}{4} \rfloor = 16383 $ 字符 - `latin1` 字符集（1字节/字符）：最大长度为 65533 字符 超过该限制将导致创建表失败[^3] 2. **decimal 的存储机制** 每9位数字占用4字节存储，计算方式为： $$ \text{存储字节} = \lceil \frac{\text{总位数}}{9} \rceil \times 4 + \lceil \frac{\text{小数位数}}{9} \rceil \times 4 $$ 3. **bigint 的显示宽度** `bigint(20)` 中的20仅影响显示格式（如 ZEROFILL 时补零），实际存储范围固定为： - 有符号：$-2^{63}$ 到 $2^{63}-1$ ($\approx \pm 9.22 \times 10^{18}$) - 无符号：$0$ 到 $2^{64}-1$ ($\approx 1.84 \times 10^{19}$) ### 查询结果示例： | 数据库名 | 表名 | 字段名 | 数据类型 | 长度/精度 | 完整类型定义 | 字符集 | 是否可空 | |----------|--------|--------|----------|-----------|--------------------|---------|----------| | shop | orders | amount | decimal | 15,6 | decimal(15,6) | NULL | NO | | user | profile| id | bigint | 19 | bigint(20) UNSIGNED| NULL | NO | | blog | posts | title | varchar | 255 | varchar(255) | utf8mb4 | YES | ---