大网数据广告活动
服务器运维问答

Why does MariaDB CHAR_LENGTH return NULL instead of 0 when a field is NULL?

· 52CE 我爱测

I am trying to calculate to total size of stored metadata in a table using CHAR_LENGTH to add up the size of the relevant fields in each row to get a rough idea of the storage size of a result set. I don't want the size of all th…

问题原文

原帖链接 · Tim · CC BY-SA 4.0

I am trying to calculate to total size of stored metadata in a table using CHAR_LENGTH to add up the size of the relevant fields in each row to get a rough idea of the storage size of a result set.

I don't want the size of all the data in the table, only the size of a set of results.

However, some fields in the table have NULL values and as soon as CHAR_LENGTH hits one of those the whole calculation fails because CHAR_LENGTH returns NULL and not 0.

For example:

SELECT SUM(row_size) as `total` FROM ( SELECT CHAR_LENGTH(`field1`)+CHAR_LENGTH(`field2`)+CHAR_LENGTH(`field3`)+CHAR_LENGTH(`field4`) as `row_size` FROM `meta_data` WHERE `id` = 1 AND `id2` = 2 ) as tbl1;

For some queries this works and I get the size in bytes but for any that have a NULL value on any field in any row of the results I get nothing.

Why would CHAR_LENGTH not return 0 for a NULL value?

I suppose the next best thing is to get the results back and iterate through them in code to add up the size. I was hoping to avoid doing that but it might be the only way?

已采纳解答

原帖链接 · Rick James · CC BY-SA 4.0

Surround each with COALESCE(..., 0).

mysql> SELECT CHAR_LENGTH(null), CHAR_LENGTH(''), CHAR_LENGTH('abc');
+-------------------+-----------------+--------------------+
| CHAR_LENGTH(null) | CHAR_LENGTH('') | CHAR_LENGTH('abc') |
+-------------------+-----------------+--------------------+
|              NULL |               0 |                  3 |
+-------------------+-----------------+--------------------+

mysql> SELECT COALESCE(CHAR_LENGTH(null), 0);
+--------------------------------+
| COALESCE(CHAR_LENGTH(null), 0) |
+--------------------------------+
|                              0 |
+--------------------------------+

mysql> SELECT LENGTH('') AS len, CHAR_LENGTH('') AS char_len;
+-----+----------+
| len | char_len |
+-----+----------+
|  16 |        4 |
+-----+----------+

Be aware that there is overhead in any string -- maybe a comma between them, maybe a newline, maybe something else.

Also, CHAR_LENGTH() gives a character count, not a byte count.

摘自公开归档,保留原文语言,版式经整理,外部媒体与邮箱地址已移除。

资料来源与适用范围

资料来源
Server Fault
适用范围
以原帖系统、软件版本与发布日期为准(2021-07-09)
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY-SA 4.0

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。