MySQL、Oracle查看字节和字符长度个数的函数

0. 总结

database	char	byte
MySQL	char_length()	length()
Oracle	length()	lengthB()

1. MySQL

1.1. 造数据

drop table if exists demo;
create table demo
(id        bigint ,timestamp timestamp(6),utf8      varchar(20) character set utf8mb4 comment 'utf8',gbk       varchar(20) character set gbk     comment 'gbk'
);insert into demo (id, timestamp, utf8, gbk) values (1    , '2024-04-01 00:05:46'       , '12345'     , '12345'     );
insert into demo (id, timestamp, utf8, gbk) values (67890, '2024-04-01 00:05:46.123456', '六七八九零', '六七八九零');-- select * from demo;

截图

1.2. 查看字符/字节个数

selectid        ,char_length(id)        as char_len1, length(id)        as byte_len1,timestamp ,char_length(timestamp) as char_len2, length(timestamp) as byte_len2,utf8      ,char_length(utf8)      as char_len3, length(utf8)      as byte_len3,gbk       ,char_length(gbk)       as char_len4, length(gbk)       as byte_len4
from demo;

截图

2. Oracle

oracle不支持单独设置某个表、某个字段的编码字符集，跟随数据库编码

-- 查看数据编码字符集
select value as nls_character_set from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
select value as nls_character_set from v$nls_parameters        where parameter = 'NLS_CHARACTERSET';

2.1. 造数据

drop table demo;
create table demo
(id        number(8)   ,timestamp timestamp(6),utf8      varchar2(20)
);insert into demo (id, timestamp, utf8) values (1    , TO_TIMESTAMP('2024-04-01 00:05:46'       , 'YYYY-MM-DD HH24:MI:SS.FF6'), '12345'    );
insert into demo (id, timestamp, utf8) values (67890, TO_TIMESTAMP('2024-04-01 00:05:46.123456', 'YYYY-MM-DD HH24:MI:SS.FF6'), '六七八九零');-- select * from demo;

截图

2.2. 查看字符/字节个数

selectid        ,length(id)        as char_len1, lengthb(id)        as byte_len1,timestamp ,length(timestamp) as char_len2, lengthb(timestamp) as byte_len2,utf8      ,length(utf8)      as char_len3, lengthb(utf8)      as byte_len3
from demo;