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)<br>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|java.lang.String|
|可変長文字列|CHARACTER VARYING(n)|最大桁数 n 以下の文字列を格納する。|java.lang.Double|java.lang.String|
|**３、ブール型**||
|ブール値|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標準
```sql: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
```sql: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
```sql: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
```sql: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
  ```sql: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
```sql:ANSI
numeric(precision, scale)
```
- precision：10進の精度、小数点と記号を含まない全体桁数
- scale：10進の位取り、小数点以下の桁数


<b>精度未定義場合</b>、以下のように各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
```sql: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進の１桁整数<br>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|代替案：<br>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をよく遵守していますね。

## リファレンス
https://webstore.ansi.org/

https://kikakurui.com/x3/index.html


https://docs.oracle.com/cd/F19136_01/sqlrf/Data-Types.html#GUID-0BC16006-32F1-42B1-B45E-F27A494963FF

https://learn.microsoft.com/ja-jp/office/client-developer/access/desktop-database-reference/equivalent-ansi-sql-data-types

https://www.postgresql.jp/docs/9.4/datatype.html

https://docs.oracle.com/cd/E57425_01/121/ZZPRE/GUID-9F34D892-C67C-4567-94C6-E402BB079FA8.htm

https://docs.oracle.com/cd/G11854_01/sqlrf/Data-Types.html#GUID-A3C0D836-BADB-44E5-A5D4-265BA5968483

https://dev.mysql.com/doc/refman/8.0/ja/integer-types.html