varcharの文字数とバイト数

• MySQL

(8.0.21)

drop table tab1;

create table tab1(col1 int, col2 varchar(3));

insert into tab1 values(1,'a');
insert into tab1 values(2,'ab');
insert into tab1 values(3,'abc');
insert into tab1 values(4,'abcd');
insert into tab1 values(11,'あ');
insert into tab1 values(12,'あい');
insert into tab1 values(13,'あいう');
insert into tab1 values(14,'あいうえ');

select col1,col2,char_length(col2),length(col2) from tab1;

→varcharでの指定は文字数

• Oracle

(19c)

drop table tab1 purge;
drop table tab2 purge;
create table tab1(col1 int, col2 varchar2(3));
create table tab2(col1 int, col2 nvarchar2(3));

insert into tab1 values(1,'a');
insert into tab1 values(2,'ab');
insert into tab1 values(3,'abc');
insert into tab1 values(4,'abcd');
insert into tab1 values(11,'あ');
insert into tab1 values(12,'あい');
insert into tab1 values(13,'あいう');
insert into tab1 values(14,'あいうえ');

insert into tab2 values(1,'a');
insert into tab2 values(2,'ab');
insert into tab2 values(3,'abc');
insert into tab2 values(4,'abcd');
insert into tab2 values(11,'あ');
insert into tab2 values(12,'あい');
insert into tab2 values(13,'あいう');
insert into tab2 values(14,'あいうえ');
commit;

select col1,col2,length(col2),lengthb(col2) from tab1;
select col1,col2,length(col2),lengthb(col2) from tab2;

→varchar2での指定はバイト数、nvarchar2での指定は文字数

• PostgreSQL

(13)

drop table tab1;

create table tab1(col1 int, col2 varchar(3));

insert into tab1 values(1,'a');
insert into tab1 values(2,'ab');
insert into tab1 values(3,'abc');
insert into tab1 values(4,'abcd');
insert into tab1 values(11,'あ');
insert into tab1 values(12,'あい');
insert into tab1 values(13,'あいう');
insert into tab1 values(14,'あいうえ');

select col1,col2,char_length(col2),octet_length(col2) from tab1;

→varcharでの指定は文字数

• SQL Server

(2019)

https://docs.microsoft.com/ja-jp/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql?view=sql-server-ver15

drop table tab1;
drop table tab2;
create table tab1(col1 int, col2 varchar(3));
create table tab2(col1 int, col2 nvarchar(3));

insert into tab1 values(1,'a');
insert into tab1 values(2,'ab');
insert into tab1 values(3,'abc');
insert into tab1 values(4,'abcd');
insert into tab1 values(11,'あ');
insert into tab1 values(12,'あい');
insert into tab1 values(13,'あいう');
insert into tab1 values(14,'あいうえ');

insert into tab2 values(1,'a');
insert into tab2 values(2,'ab');
insert into tab2 values(3,'abc');
insert into tab2 values(4,'abcd');
insert into tab2 values(11,'あ');
insert into tab2 values(12,'あい');
insert into tab2 values(13,'あいう');
insert into tab2 values(14,'あいうえ');

select col1,col2,len(col2),datalength(col2) from tab1;
select col1,col2,len(col2),datalength(col2) from tab2;

→varcharでの指定はバイト数、nvarcharでの指定は文字数

※NVARCHAR(n) では、n によって文字長がバイトペア (0-4,000) で定義されます。
 n は、格納できる文字数を定義しません。
補助文字によって 2 つのバイト ペア (またはサロゲート ペア) が使用されるため、
格納できる文字数は n よりも少なくなる場合があります。

updateでのselect権限必要性

• MySQL

(8.0.21)

-- テストテーブル作成
drop table tab1;
create table tab1(col1 int);
alter table tab1 add constraint tab1pk primary key(col1);

insert into tab1 values(1);
select * from tab1;

-- テストユーザ作成
drop user 'user1'@'%';
create user 'user1'@'%' identified by 'user1';

-- 動作確認
revoke select on test.tab1 from user1;
revoke update on test.tab1 from user1;
revoke delete on test.tab1 from user1;

grant select on test.tab1 to user1;
grant update on test.tab1 to user1;
grant delete on test.tab1 to user1;

show grants for 'user1'@'%';

start transaction;

select * from test.tab1;
update test.tab1 set col1 = 1;
update test.tab1 set col1 = 1 where col1 = 1;
delete from test.tab1;
delete from test.tab1 where col1 = 1;

rollback;

→ updateやdeleteの実行でwhere句がある場合はselect権限も必要

• Oracle

(12cR1)

-- テストテーブル作成
drop table tab1 purge;
create table tab1(col1 int);
alter table tab1 add constraint tab1pk primary key(col1);

insert into tab1 values(1);
commit;
select * from tab1;

-- テストユーザ作成
drop user user1 cascade;
create user user1 identified by user1;

grant create session to user1;

-- 動作確認
revoke select on tab1 from user1;
revoke update on tab1 from user1;
revoke delete on tab1 from user1;

grant select on tab1 to user1;
grant update on tab1 to user1;
grant delete on tab1 to user1;

select * from dba_tab_privs where TABLE_NAME = 'TAB1';

select * from test.tab1;
update test.tab1 set col1 = 1;
update test.tab1 set col1 = 1 where col1 = 1;
delete from test.tab1;
delete from test.tab1 where col1 = 1;

rollback;

→ updateやdeleteの実行にselect権限は不要

SQL92_SECURITYで制御される
(12cR1)のデフォルトはfalseのため、select権限は不要
(19c)のデフォルトはtrueのため、select権限は必要

• PostgreSQL

(11)

-- テストテーブル作成
drop table tab1;
create table tab1(col1 int);
alter table tab1 add constraint tab1pk primary key(col1);

insert into tab1 values(1);
select * from tab1;

-- テストユーザ作成
drop owned by user1 cascade;
drop user user1;
create user user1 with login encrypted password 'user1';

-- 動作確認

revoke select on tab1 from user1;
revoke update on tab1 from user1;
revoke delete on tab1 from user1;

grant select on tab1 to user1;
grant update on tab1 to user1;
grant delete on tab1 to user1;

\z tab1

start transaction;

select * from tab1;
update tab1 set col1 = 1;
update tab1 set col1 = 1 where col1 = 1;
delete from tab1;
delete from tab1 where col1 = 1;

rollback;

→ updateやdeleteの実行でwhere句がある場合はselect権限も必要

• SQL Server

(2019)

-- テストテーブル作成
use test
go
drop table tab1;
create table tab1(col1 int not null);
alter table tab1 add constraint tab1pk primary key(col1);

insert into tab1 values(1);
select * from tab1;

-- テストユーザ作成
use test
go

drop user user1;
go

use master
go

drop login user1;
go

create login user1 with password='user1', default_database=test, check_policy=off
go

use test
go

create user user1 for login user1;

-- 動作確認

revoke select on tab1 from user1;
revoke update on tab1 from user1;
revoke delete on tab1 from user1;

grant select on tab1 to user1;
grant update on tab1 to user1;
grant delete on tab1 to user1;

select * from sys.database_principals;
select * from sys.database_permissions;

begin transaction;

select * from tab1;
update tab1 set col1 = 1;
update tab1 set col1 = 1 where col1 = 1;
delete from tab1;
delete from tab1 where col1 = 1;

rollback;

→ updateやdeleteの実行でwhere句がある場合はselect権限も必要

パスワードポリシー

• MySQL

(8.0.21)
https://tokyo-engineer.com/mysql8-password-root/#i
https://dev.mysql.com/doc/refman/8.0/en/validate-password-options-variables.html

※デフォルトで有効

SHOW VARIABLES LIKE 'validate_password.%';

+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 8 |
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | MEDIUM |
| validate_password.special_char_count | 1 |
+--------------------------------------+--------+

vim /etc/my.cnf

validate_password.length = 4
validate_password.policy = LOW
validate_password.check_user_name = OFF

systemctl restart mysqld

SHOW VARIABLES LIKE 'validate_password.%';

+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| validate_password.check_user_name | OFF |
| validate_password.dictionary_file | |
| validate_password.length | 4 |
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | LOW |
| validate_password.special_char_count | 1 |
+--------------------------------------+-------+

• Oracle

(12cR1)
https://blogs.oracle.com/sec/howto_010_password_profile

※デフォルトで無効

select * from dba_profiles where PROFILE = 'DEFAULT' and RESOURCE_TYPE = 'PASSWORD';

alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;
alter profile default limit PASSWORD_LIFE_TIME unlimited;
alter profile default limit PASSWORD_LOCK_TIME unlimited;
alter profile default limit PASSWORD_GRACE_TIME unlimited;

• PostgreSQL

(11)
https://lets.postgresql.jp/node/159

※デフォルトで無効

-- passwordcheck

vim postgresql.conf

shared_preload_libraries = '$libdir/passwordcheck'

sudo systemctl restart postgresql-11

create user user1 with login encrypted password '1234567';
ERROR: password is too short

create user user1 with login encrypted password '12345678';
ERROR: password must contain both letters and nonletters

create user user1 with login encrypted password 'user1user1';
ERROR: password must not contain user name

• SQL Server

(2019)
https://docs.microsoft.com/ja-jp/sql/relational-databases/security/password-policy?view=sql-server-ver15

※デフォルトで有効

use master
go

drop login user1;

create login user1 with password='1234567', check_policy=on
create login user1 with password='12345678', check_policy=on
create login user1 with password='user1user1', check_policy=on

パスワードの検証に失敗しました。このパスワードはオペレーティング システムのポリシーで指定された基準を満たすほど複雑ではありません。

アーカイブログサイズ調査

• MySQL

(8.0.21)

-- テストテーブル作成
drop table tab1;
drop table tab2;

create table tab1(
col1 numeric(10,0),
col2 numeric(10,0),
col3 numeric(10,0),
col4 numeric(10,0),
col5 numeric(10,0),
col6 varchar(30),
col7 varchar(30),
col8 varchar(30),
col9 varchar(30),
col10 varchar(30),
col11 timestamp,
col12 timestamp,
col13 timestamp,
col14 timestamp,
col15 timestamp
);

alter table tab1 add constraint tab1pk primary key(col1);
create table tab2 as select * from tab1 where 1=2;

-- テストデータ投入

drop procedure proc1;

delimiter //
create procedure proc1()
begin
declare i bigint;
set i = 1;
while i <= 1000000 do
insert into tab1 values(
i,
floor(rand() * 1000000000)+1,
floor(rand() * 1000000000)+1,
floor(rand() * 1000000000)+1,
floor(rand() * 1000000000)+1,
substring(md5(rand()), 1, 30),
substring(md5(rand()), 1, 30),
substring(md5(rand()), 1, 30),
substring(md5(rand()), 1, 30),
substring(md5(rand()), 1, 30),
date_add('2001-01-01', interval floor(3650 * rand()) day),
date_add('2001-01-01', interval floor(3650 * rand()) day),
date_add('2001-01-01', interval floor(3650 * rand()) day),
date_add('2001-01-01', interval floor(3650 * rand()) day),
date_add('2001-01-01', interval floor(3650 * rand()) day)
);
set i = i + 1;
end while;
end
//

delimiter ;

call proc1();

select count(*) from tab1;
select count(*) from tab2;

select floor( (data_length)/1024/1024 )
from information_schema.tables
where table_schema= 'test'
and table_name = 'tab1';

analyze table tab2;
select floor( (data_length)/1024/1024 )
from information_schema.tables
where table_schema= 'test'
and table_name = 'tab2';

-- アーカイブログサイズ確認

reset master;

cd /var/lib/mysql
du -sh arc

-- アーカイブログ生成

truncate table tab2;
insert into tab2 select * from tab1;

truncate table tab2;
start transaction;
insert into tab2 select * from tab1;
commit;

-- 結果

---- ログフォーマット=ROW、毎回コミットの場合
元データサイズ：233MB
アーカイブログサイズ：194MB

---- ログフォーマット=ROW、まとめてコミットの場合
元データサイズ：233MB
アーカイブログサイズ：256MB

アーカイブログサイズは元データとほぼ同じサイズとなる

※ログフォーマット=STATEMENTの場合、アーカイブログサイズはほぼ0

• Oracle

(12cR1)

-- テストテーブル作成
drop table tab1 purge;
drop table tab2 purge;

create table tab1(
col1 number(10,0),
col2 number(10,0),
col3 number(10,0),
col4 number(10,0),
col5 number(10,0),
col6 varchar2(30),
col7 varchar2(30),
col8 varchar2(30),
col9 varchar2(30),
col10 varchar2(30),
col11 timestamp,
col12 timestamp,
col13 timestamp,
col14 timestamp,
col15 timestamp
);

create unique index ind1 on tab1(col1);
alter table tab1 add constraint tab1pk primary key(col1) using index ind1;

create table tab2 as select * from tab1 where 1=2;

-- テストデータ投入

declare
begin
for i in 1..1000000 loop
insert into tab1 values(
i,
floor(dbms_random.value(1, 1000000001)),
floor(dbms_random.value(1, 1000000001)),
floor(dbms_random.value(1, 1000000001)),
floor(dbms_random.value(1, 1000000001)),
substr(standard_hash(dbms_random.value(), 'MD5'),1,30),
substr(standard_hash(dbms_random.value(), 'MD5'),1,30),
substr(standard_hash(dbms_random.value(), 'MD5'),1,30),
substr(standard_hash(dbms_random.value(), 'MD5'),1,30),
substr(standard_hash(dbms_random.value(), 'MD5'),1,30),
to_date('20010101','yyyymmdd') + floor(dbms_random.value(0, 3650)),
to_date('20010101','yyyymmdd') + floor(dbms_random.value(0, 3650)),
to_date('20010101','yyyymmdd') + floor(dbms_random.value(0, 3650)),
to_date('20010101','yyyymmdd') + floor(dbms_random.value(0, 3650)),
to_date('20010101','yyyymmdd') + floor(dbms_random.value(0, 3650))
);
commit;
end loop;
end;
/

select count(*) from tab1;
select count(*) from tab2;
select sum(bytes/1024/1024) MB from user_segments where segment_name ='TAB1';
select sum(bytes/1024/1024) MB from user_segments where segment_name ='TAB2';

-- アーカイブログサイズ確認
delete noprompt archivelog all;

cd /u01/app/oracle/product/12.1.0/dbhome_1/dbs
du -sh .

-- アーカイブログ生成

truncate table tab2;
declare
cursor cur1 is select * from tab1;
begin
for c1 in cur1 loop
insert into tab2 values(
c1.col1,
c1.col2,
c1.col3,
c1.col4,
c1.col5,
c1.col6,
c1.col7,
c1.col8,
c1.col9,
c1.col10,
c1.col11,
c1.col12,
c1.col13,
c1.col14,
c1.col15
);
commit;
end loop;
end;
/

truncate table tab2;
declare
cursor cur1 is select * from tab1;
begin
for c1 in cur1 loop
insert into tab2 values(
c1.col1,
c1.col2,
c1.col3,
c1.col4,
c1.col5,
c1.col6,
c1.col7,
c1.col8,
c1.col9,
c1.col10,
c1.col11,
c1.col12,
c1.col13,
c1.col14,
c1.col15
);
end loop;
end;
/
commit;

-- 結果

---- 毎回コミットの場合
元データサイズ：256MB
アーカイブログサイズ：819MB

---- まとめてコミットの場合
元データサイズ：256MB
アーカイブログサイズ：550MB

アーカイブログサイズは元データのおよそ2～3倍となる

• PostgreSQL

(11)

-- テストテーブル作成
drop table tab1;
drop table tab2;

create table tab1(
col1 numeric(10,0),
col2 numeric(10,0),
col3 numeric(10,0),
col4 numeric(10,0),
col5 numeric(10,0),
col6 varchar(30),
col7 varchar(30),
col8 varchar(30),
col9 varchar(30),
col10 varchar(30),
col11 timestamp,
col12 timestamp,
col13 timestamp,
col14 timestamp,
col15 timestamp
);

create unique index ind1 on tab1(col1);
alter table tab1 add constraint tab1pk primary key using index ind1;

create table tab2 as select * from tab1 where 1=2;

-- テストデータ投入

do $$
declare
begin
for i in 1..1000000 loop
insert into tab1 values(
i,
floor(random() * 1000000000)+1,
floor(random() * 1000000000)+1,
floor(random() * 1000000000)+1,
floor(random() * 1000000000)+1,
substring(md5(random()::text), 1, 30),
substring(md5(random()::text), 1, 30),
substring(md5(random()::text), 1, 30),
substring(md5(random()::text), 1, 30),
substring(md5(random()::text), 1, 30),
'2001-01-01'::date + CAST( floor(3650 * random()) || 'days' AS interval),
'2001-01-01'::date + CAST( floor(3650 * random()) || 'days' AS interval),
'2001-01-01'::date + CAST( floor(3650 * random()) || 'days' AS interval),
'2001-01-01'::date + CAST( floor(3650 * random()) || 'days' AS interval),
'2001-01-01'::date + CAST( floor(3650 * random()) || 'days' AS interval)
);
end loop;
end
$$
;

select count(*) from tab1;
select count(*) from tab2;
select pg_size_pretty(pg_relation_size('tab1'));
select pg_size_pretty(pg_relation_size('tab2'));

-- アーカイブログサイズ確認

cd /var/lib/pgsql/11/data
rm -rf arc/*
du -sh arc
du -sh pg_wal

-- アーカイブログ生成

truncate table tab2;
insert into tab2 select * from tab1;

truncate table tab2;
start transaction;
insert into tab2 select * from tab1;
commit;

-- 結果

---- 毎回コミットの場合
元データサイズ：260MB
アーカイブログサイズ：289MB

---- まとめてコミットの場合
元データサイズ：260MB
アーカイブログサイズ：401MB

アーカイブログサイズは元データのおよそ1～1.5倍となる

• SQL Server

(2019)

-- テストテーブル作成
drop table tab1;
drop table tab2;

create table tab1(
col1 numeric(10,0) not null,
col2 numeric(10,0),
col3 numeric(10,0),
col4 numeric(10,0),
col5 numeric(10,0),
col6 varchar(30),
col7 varchar(30),
col8 varchar(30),
col9 varchar(30),
col10 varchar(30),
col11 datetime2,
col12 datetime2,
col13 datetime2,
col14 datetime2,
col15 datetime2
);

create unique index ind1 on tab1(col1);
alter table tab1 add constraint tab1pk primary key(col1);

select * into tab2 from tab1 where 1=2;

-- テストデータ投入

declare @counter int;
set @counter = 1;

while (@counter <= 1000000)
begin
insert into tab1 values (
@counter,
floor(rand() * 1000000000)+1,
floor(rand() * 1000000000)+1,
floor(rand() * 1000000000)+1,
floor(rand() * 1000000000)+1,
substring(master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar))),3,30),
substring(master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar))),3,30),
substring(master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar))),3,30),
substring(master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar))),3,30),
substring(master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar))),3,30),
dateadd(day,floor(3650 * rand()),'2001-01-01'),
dateadd(day,floor(3650 * rand()),'2001-01-01'),
dateadd(day,floor(3650 * rand()),'2001-01-01'),
dateadd(day,floor(3650 * rand()),'2001-01-01'),
dateadd(day,floor(3650 * rand()),'2001-01-01')
);
set @counter = @counter + 1;
end

select count(*) from tab1;
select count(*) from tab2;

exec sp_spaceused 'dbo.tab1';
go

exec sp_spaceused 'dbo.tab2';
go

-- アーカイブログサイズ確認

use test;
go
backup log test
to disk = 'C:\backup\bk01.bak'
with format, medianame = 'bk01', name = 'log backup test';

cd C:\backup
dir

-- アーカイブログ生成

truncate table tab2;
insert into tab2 select * from tab1;

truncate table tab2;
begin transaction;
insert into tab2 select * from tab1;
commit;

-- 結果

---- 毎回コミットの場合
元データサイズ：252MB
アーカイブログサイズ：360MB

---- まとめてコミットの場合
元データサイズ：252MB
アーカイブログサイズ： 360MB

アーカイブログサイズは元データのおよそ1.5倍となる

月末月初テスト用シェル

systemctl stop chronyd
systemctl status chronyd

vim change_time.sh

#!/bin/bash

MONTH01=20210301
INTERVAL=7200

while true;do
MONTH01=`date --date "${MONTH01} 1 month" '+%Y%m01'`
SET_TIME=`date --date "${MONTH01} -1 day" '+%Y/%m/%d 23:00:00'`
date -s "${SET_TIME}"
ping localhost -c ${INTERVAL} > /dev/null
done