ANSI データ型定義と各DBの実装状況

前言

会社のパッケージ作成にあたり多言語対応のため、標準SQLのDDLの書き方に関していろいろ調べて、よく使うデータ型の各DBの実装状況をまとめてみました。

ANSIの既定義型

データ型	既定名称	説明	Javaデータ型
１、数型
１－１、真数型
狭範囲整数	SMALLINT	2バイト(16ビット)整数	java.lang.Integer
典型的整数	INTEGER	4バイト(32ビット)整数	java.lang.Integer
広範囲整数	BIGINT	8バイト(64ビット)整数	java.lang.Long
固定小数点数（可変長）	DECIMAL(p, s) NUMERIC(p, s)	有効桁数と小数点以下桁数が固定された数値	java.math.BigDecimal
１－２、概数型
単精度的小数	REAL	可変精度4バイト(32ビット)	java.lang.Float
倍精度的小数	DOUBLE PRECISION	可変精度8バイト(64ビット)	java.lang.Double
浮動小数点数	FLOAT | FLOAT(n)	2進数桁数のnにより単精度にするか倍精度にするか決める小数、n未定義場合倍精度	java.lang.Double
２、文字列型
固定長文字列	CHARACTER(n)	NULLではない場合、最大桁数 n まで空白埋めを行う。	java.lang.Double
可変長文字列	CHARACTER VARYING(n)	最大桁数 n 以下の文字列を格納する。	java.lang.Double
３、ブール型
ブール値	BOOLEAN	真 (true) 又は偽 (false) を格納する。	java.lang.Boolean
４、日時型
日付	DATE	日付	java.sql.Date
時刻	TIME	時刻	java.sql.Time
日時	TIMESTAMP	タイムスタンプ	java.sql.Timestamp

また、あまりよく利用しないデータ型はいくつかあります。調査対象外にします。

既定名称
CHARACTER LARGE OBJECT
BINARY
BINARY VARYING
BINARY LARGE OBJECT
INTERVAL

ANSIデータ型の対応状況

調査方法は、以下のANSI標準SQLを各種類DBに実行して、作成されたテーブルのDDLを微調整して、同等データ型を探すことです。

ANSI標準

ANSI標準SQL

CREATE TABLE "項目種類テスト"
(
  id integer primary key,
  character_fd character(10),
  character_varying_fd character varying(10),
  int_fd int,
  smallint_fd smallint,
  numeric_fd numeric,
  decimal_fd decimal,
  float_fd float,
  real_fd real,
  double_precision_fd double precision,
  boolean_fd boolean,
  date_fd date,
  time_fd time,
  timestamp_fd timestamp
);

各DB作成されたテーブルのDDLです。

PostgreSQL 17.2

PostgreSQL

CREATE TABLE "項目種類テスト"
(
  id integer NOT NULL,
  character_fd character(10),
  character_varying_fd character varying(10),
  integer_fd integer,
  smallint_fd smallint,
  numeric_fd numeric,
  decimal_fd numeric,
  float_fd double precision,
  real_fd real,
  double_precision_fd double precision,
  boolean_fd boolean,
  date_fd date,
  time_fd time without time zone,
  timestamp_fd timestamp without time zone,
  CONSTRAINT "項目種類テスト_pkey" PRIMARY KEY (id)
)

SQLServer 2022EXPRESS

SQLServer

CREATE TABLE [dbo].[項目種類テスト](
	[id] [int] NOT NULL,
	[character_fd] [char](10) NULL,
	[character_varying_fd] [varchar](10) NULL,
	[int_fd] [int] NULL,
	[smallint_fd] [smallint] NULL,
	[numeric_fd] [numeric](18, 0) NULL,
	[decimal_fd] [decimal](18, 0) NULL,
	[float_fd] [float] NULL,
	[real_fd] [real] NULL,
	[double_precision_fd] [float] NULL,
	[boolean_fd] [bit] NULL,--※３、ANSIのbooleanと同等
	[date_fd] [date] NULL,
	[time_fd] [time](7) NULL,
	[timestamp_fd] [datetime2](7) NULL,--日時(ミリ秒まで)の意味ならdatetime2ちょうどよい
PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
)

Oracle 21cEXPRESS

Oracle

CREATE TABLE "SYSTEM"."項目種類テスト" (
    "ID" NUMBER(*,0), 
	"CHARACTER_FD" CHAR(10), 
	"CHARACTER_VARYING_FD" VARCHAR2(10), 
	"INT_FD" NUMBER(*,0), 
	"SMALLINT_FD" NUMBER(*,0), 
	"NUMERIC_FD" NUMBER(*,0), 
	"DECIMAL_FD" NUMBER(*,0), 
	"FLOAT_FD" FLOAT(126), 
	"REAL_FD" FLOAT(63), 
	"DOUBLE_PRECISION_FD" FLOAT(126), 
	"BOOLEAN_FD" NUMBER(1,0), --１桁整数で代替する
	"DATE_FD" DATE, --日付のみのタイプはない
	"TIME_FD" DATE, --時間のみのタイプはない
	"TIMESTAMP_FD" TIMESTAMP (6), 
	 PRIMARY KEY ("ID")
   ) 
 

MySQL 9.2

MySQL

CREATE TABLE `項目種類テスト` (
`id` int NOT NULL,
`character_fd` char(10) COLLATE utf8mb3_bin DEFAULT NULL,
`character_varying_fd` varchar(10) COLLATE utf8mb3_bin DEFAULT NULL,
`int_fd` int DEFAULT NULL,
`smallint_fd` smallint DEFAULT NULL,
`numeric_fd` decimal(10,0) DEFAULT NULL,
`decimal_fd` decimal(10,0) DEFAULT NULL,
`float_fd` double DEFAULT NULL,
`real_fd` float DEFAULT NULL,
`double_precision_fd` double DEFAULT NULL,
`boolean_fd` tinyint(1) DEFAULT NULL,
`date_fd` date DEFAULT NULL,
`time_fd` time DEFAULT NULL,
`timestamp_fd` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) 

注意点

１、numericとdecimal

ANSI

numeric(precision, scale)

• precision：10進の精度、小数点と記号を含まない全体桁数
• scale：10進の位取り、小数点以下の桁数

精度未定義場合、以下のように各DBの振舞いが違います。

ANSI	numeric	decimal	説明
PostgreSQL	numeric	numeric	何でも格納
SQLServer	[numeric](18, 0)	[decimal](18, 0)	整数
Oracle	NUMBER(*,0)	NUMBER(*,0)	整数
MySQL	decimal(10,0)	decimal(10,0)	整数

２、float

ANSI

float(precision)

• precision：2進の精度

精度によりfloatは:realかdouble precisionに変わります。各DBにそれらのキーワードとマッピングするものがあります。

ANSI	精度:1～24	精度:25～53	精度未定義
PostgreSQL	real	double precision	double precision
SQLServer	[real]	[float]	[float]
Oracle	FLOAT(63)	FLOAT(126)	FLOAT(126)
MySQL	float	double	double

３、boolean

ANSI	boolean	説明
PostgreSQL	boolean	１ビット
SQLServer	[bit]	１ビット
Oracle	代替案：NUMBER(1,0)	10進の１桁整数 javaで取り扱い時、数字を取得される
MySQL	tinyint(1)	１バイト

４、date

ANSI	date	格納データ
PostgreSQL	date	YYYY-MM-DD
SQLServer	date	YYYY-MM-DD
Oracle	DATE	YYYY-MM-DD HH:MI:SS
MySQL	date	YYYY-MM-DD

５、time

ANSI	time	格納データ
PostgreSQL	time	HH:MI:SS
SQLServer	[time](7)	HH:MI:SS
Oracle	代替案： DATE	YYYY-MM-DD HH:MI:SS
MySQL	time	HH:MI:SS

６、timestamp

ANSI	timestamp	格納データ	説明
PostgreSQL	timestamp	HH:MI:SS.sss	３桁はミリ秒
SQLServer	[datetime2](7)	HH:MI:SS.sssssss	７桁❓
Oracle	timestamp	YYYY-MM-DD HH:MI:SS.sssssssss	９桁はナノ秒
MySQL	timestamp	HH:MI:SS.ssssss	６桁はマイクロ秒

java.sql.Dateは、ミリ秒まで格納できます。java.sql.Timestampは、ナノ秒まで格納できます。６桁と７桁はちょっとがっかりですね。

感想

PostgreSQLはANSIをよく遵守していますね。

リファレンス