0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

SQLのデータ型全部まとめた

Last updated at Posted at 2024-06-14

はじめに

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

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

※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)
参考サイトその2
公式ドキュメント

boolean

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

DECIMAL

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

最大桁数の DECIMAL(65, 30) は「精度(Precision): 65桁」「スケール(Scale): 30桁」の特性をもつ
これは、整数部分は最大で35桁まで格納でき、小数部分は最大で30桁まで格納できる。

操作関数

関数 意味
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 HH*ii*ss`, `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() 2つの日付の差分
DAYOFWEEK() 曜日インデックス(1:日曜)
WEEKDAY() 曜日インデックス(0:月曜)
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 は可変長なので、「格納するデータのバイト数+1(末尾の'\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を3桁区切りの文字列にし、
小数点以下D桁に丸める
lower(str) 小文字化
upper(str) 大文字化
replace(str,from_str,to_str) 置換

空白の削除

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

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

さいごに

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

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?