はじめに

データベース設計してるとデータ型で頭がいっぱいになったので、
ここに書き出しておく

~~過去にまとめた記事の補強です~~

※MySQLを基準としています
SQL server と Oracle でもそんな変わらん

※すべてのデータ型や操作関数を列挙しているわけではありません
よく使うらしいものをピックアップしています

数値型

種類

M：桁数、D：小数点以下の桁数

種類	バイト長	意味
TINYINT[(M)]	1	整数
SMALLINT[(M)]	2	整数
MEDIUMINT[(M)]	3	整数
BIGINT[(M)]	8	整数
FLOAT[(M,D)]	4	浮動少数点

REAL[(M,D)]	8	浮動少数点
DECIMAL[(M,[D])]	-	固定小数点数（最大65桁）小数点以下の最大桁数は30

BOOL	-	0はfalse、それ以外はtrue
BIT[(M)]	-	ビット列（最大64桁）

整数

INTEGER = int

タイプ	サイズ(byte)	最小値	最大値
TINYINT	1	-128, 0	127, 255
SMALLINT	2	-32768, 0	32767, 65535
MEDIUMINT	3	-8388608, 0	8388607, 16777215
INT	4	-2147483648, 0	2147483647, 4294967295
BIGINT	8	-9223372036854775808, 0	9223372036854775807, 18446744073709551615

Mysql(MariaDB) では、 INT(4) のように型指定の後に整数値を指定できるが、これは実は「バイト数」ではなく「表示桁数」である。
よって、カラムに格納できる値の範囲が制限されたりすることはなく、実際には11桁（INT型の最大値）まで登録できる。

では、 (?) のカッコ内の数字は何なのか？
オプション属性の ZEROFILL を使用すると、足りない桁は 0（ゼロ）が埋め込まれる（ゼロパディングされる）。 (?) のカッコ内の数字は、その際の桁数である。
したがって、INT(4) ZEROFILL に 9 という数字を格納すると 0009 と表示される。

参考サイト(old)
参考サイトその２
 公式ドキュメント

boolean

内部的には int(11) となる。
tiny(1) を利用するとよい。
完全に0or1にする場合は、bit(1) を利用する。

DECIMAL

必要に応じて0でパディングされ、指定小数点桁以下は切り捨てるので丸め誤差がほぼ生じない
科学計算や金融取引など、高精度な数値計算が必要な場合に適している

DECIMAL(M, D) の形式で定義される
M: 総桁数（整数部と小数部の合計桁数）。最大値は65
D: 小数点以下の桁数。最大値は30
例）DECIMAL(10, 2) : 整数部が10桁、小数部が2桁であることを意味する

操作関数

関数	意味
ROUND(X)	四捨五入
CEIL(X)	切り上げ
FLOOR(X)	切り下げ
TRUNCATE(X,D)	小数点以下D桁まで表示（切り捨て）
POW(X,Y)	べき乗
SQRT(X)	平方根
RAND()	乱数（0～1.0）
ABS(X)	絶対値
MOD(X,Y)	剰余
X MOD Y	剰余
X DIV Y	整数除算なぜか`DIV(X,Y)`はない

公式ドキュメント

日付型と時刻型

種類

種類	意味
DATE	年月日
DATETIME	年月日時刻
TIME	時刻
TIMESTAMP	タイムスタンプ
YEAR	年

DATE：日付

範囲	`1000-01-01`から`9999-12-31`
フォーマット	`YYYY-MM-DD`
入力可能フォーマット	`YY-MM-DD`, `YYYYMMDD`, `YYMMDD`, `YYYY/MM/DD`, `YYYY.MM.DD`

DATETIME：日時

範囲	`1000-01-01 00:00:00.000000`から`9999-12-31 23:59:59.999999`
フォーマット	`YYYY-MM-DD HH:ii:ss`
入力可能フォーマット：	`YY-MM-DD HH:ii:ss`, `YYYYMMDDHHiiss`, `YYMMDDHHiiss`, `YYYY/MM/DD HHiiss`, `YYYY.MM.DD HH+ii+ss`

TIMESTAMP：タイムスタンプ

意味	エポック（`1970-01-01 00:00:00` UCT）からの秒数
範囲	`1970-01-01 00:00:00.000000` UCTから `2038-01-19 03:14:07.999999` UCT

TIME：時間

範囲	`-838:59:59.000000` から `838:59:59.000000`
フォーマット	`HH-ii-ss`

YEAR：2桁または4桁の年

範囲	`1901` から `2155` と `0000`
範囲	`70` から `69` （1970から2069に相当）
フォーマット	`YYYY` or `YY`

表示フォーマット変換

date型やdatetime型の値をフォーマットで指定した文字列に整形することができる

DATE_FORMAT(date, format)
TIME_FORMAT(date, format)

SELECT DATE_FORMAT(purchase_datetime,'%Y年%m月%d日 %H時%i分') FROM events;
SELECT DATE_FORMAT('2035-11-11 03:42:09','%p%l時%i分%s秒');

参考サイト

操作関数（計算）

関数	意味
ADDDATE()	日付を加減算
ADDTIME()	時刻を加減算
DATEDIFF()	２つの日付の差分
DAYOFWEEK()	曜日インデックス（１：日曜）
WEEKDAY()	曜日インデックス（０：月曜）

sample.sql

select ADDDATE('2020-04-01',25)
select ADDDATE('2020-04-01',-25)
select ADDTIME('03:26:59',1);
select ADDTIME('03:26:59','1:00');
select DATEDIFF('2020-04-01','2024-04-01');
select DAYOFWEEK('2024-06-28');
select WEEKDAY('2024-06-28');

公式ドキュメント

現在日時の取得方法

-- 現在時刻
-- 16:48:41
select current_time();
select curtime();

-- 現在日付
-- 2024-06-14
select current_date();
select curdate();

-- 現在日時
-- 2024-06-14 16:48:42
select now();

文字列型

種類

L：文字列、M：バイト数

種類	意味
char(L)	固定長文字列 Lは0~255
national char(L)	char(L)と同じ
nchar(L)	char(L)と同じ
character(L)	char(L)と同じ
varchar(L)	可変長文字列 Lは0~255
text[(M)]	テキストデータを格納するための可変長文字列型最大M(デフォルトは65,535)バイト
tinytext	最大255バイトのテキストデータ
mediumtext	最大16,777,215バイトのテキストデータ
longtext	最大4GBのテキストデータ
blob[(M)]	バイナリデータを格納するための可変長バイナリ型最大M(デフォルトは65,535)バイト
tinyblob	最大255バイトのバイナリデータ
mediumblob	最大16,777,215バイトのバイナリデータ
longblob	最大4GBのバイナリデータ

charとvarcharのバイト数

char は固定長なので、空文字でも固定バイト数保持する
varchar は可変長なので、「格納するデータのバイト数＋１(末尾の'\0')」が必要となる
どちらも最大バイト数（L）を超える文字は切り捨てられる

公式ドキュメント

操作関数

長さ

関数	意味
length(str)	バイト数
char_length(str)	文字数

部分文字列取得

関数	意味
left(str,n)	左端からn文字取得
right(ste,n)	右端からn文字取得
mid(ste,i,n)	インデックスiからn文字取得
substr()	mid()と同じ（substring()のシノニム）
substring_index(str,delim,count)	区切り文字 delim が count 回出現する前の部分文字列を返す

位置検索

関数	意味
instr(str,substr)	最初に出現するインデックスを返す
locate(substr,str[,pos])	[インデックスpos以降で]最初に出現するインデックスを返す
position()	locate()のシノニム

変更

関数	意味
insert(str,pos,len,newstr)	posからlen文字消してnewstrを挿入実際置換なのでlenを0にすると純粋な挿入
concat(str1,str2,...)	文字列結合
format(X,D)	数値の書式設定数値Xを３桁区切りの文字列にし、小数点以下D桁に丸める
lower(str)	小文字化
upper(str)	大文字化
replace(str,from_str,to_str)	置換

空白の削除

関数	意味
ltrim(str)	先頭空白を削除
rtrim(str)	末尾空白を削除
trim(str)	先頭と末尾の空白を削除

公式ドキュメント
 参考サイト
 参考記事

さいごに

他のSQLまとめた記事たちはこちら