<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 |
---
本文介绍了一种SQL查询方法，用于查找数据库中一个商品对应多个仓库记录的情况，并提供了具体实现步骤及样例代码。
扫一扫
1462
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
