sqlite3で利用可能な型
sqlite3のストレージ型は、NULL, INTEGER(1,2,3,4,6,8バイト)、REAL(8バイト)、TEXT(UTF-8、UTF-16/16LE)、BLOBの5種類です。
SQLServer、MySQL、Oracle、PostgreSQLなど一般的なSQLサーバーは、CREATE TABLEで列に対して型を指定します。
SQLite3も同様にCREATE TABLEで列に対して型を指定します。
CREATE TABLEで列に設定する型はコメントに等しい扱いになります。
SQLite3はINSERT INTO、UPDATEで指定した値によってストレージの型が決定されます。
1 |
create table a(i int); |
以下SQLは、intに対するInsertです。valueはint以外の値も含みます。
1 2 3 4 5 |
insert into a (i) values( 0.0001 ); insert into a(i) values( 10 ); insert into a(i) values('10'); insert into a(i) values('a'); |
結果は、すべて成功します。
sqlite3は、値毎に型を持ちます。そのためCREATE TABLEで指定する型はコメントに近い意味合いになります。
typeof関数で値の型を調べることができます。先ほどINT列(i)にINSERTした値は、以下のようになります。
1 2 3 4 5 |
select typeof(i), i from a; real|0.0001 integer|10 <b>integer|10</b> <=このように文字を指定しても数値に変換される場合があります。 text|a |
このようにSQLite3データベースは、なんでも入れられる入れ物という認識を持つ必要があります。
間違った型をINSERT・UPDATEしてもsqlite3はエラーになりません。正常に挿入・更新が行えます。
SQLite3クラス、PDOクラスなどプログラムから読み書きする際は、
PHP側で型を守ることを意識したコーディングが重要になってきます。
Contents
sqlite3で利用可能な型
CREATE TABLE、CASTで指定可能な型は、下記5つです。
- INTEGER、
- REAL、
- TEXT、
- BLOB、
- NUMERIC
これ以外にも、CREATE TABLE、CASTで指定できる型があります。
VARCHAR、TINYINT、NCHAR(100)、FLOAT、DATE、DATETIMEも指定可能です。
sqlite3の内部的には、INTEGER、REAL、TEXT、NUMBERに変換され保持されます。
CREATE TABLE、CASTで指定できる型 | 内部的に保持される型 |
INTEGER | INTEGER |
INT | INTEGER |
TINYINT | INTEGER |
SMALLINT | INTEGER |
MEDIUMINT | INTEGER |
BIGINT | INTEGER |
UNSIGNED BIG INT | INTEGER |
INT2 | INTEGER |
INT8 | INTEGER |
TEXT | TEXT |
CHARACTER(20) (20)は無視されます |
TEXT |
VARCHAR(255) (255)は無視されます |
TEXT |
VARYING CHARACTER(255) (255)は無視されます |
TEXT |
NCHAR(255) (255)は無視されます |
TEXT |
NATIVE CHARACTER(255) (255)は無視されます |
TEXT |
NVARCHAR(255) (255)は無視されます |
TEXT |
CLOB | TEXT |
BLOB | BLOB |
REAL | REAL |
DOUBLE | REAL |
DOUBLE PRECISION | REAL |
FLOAT | REAL |
NUMERIC | NUMERIC |
DECIMAL(10,5) (10,5)は無視されます |
NUMERIC |
BOOLEAN | NUMERIC |
DATE | NUMERIC |
DATETIME | NUMERIC |
SQLite3のスキーマとしては、VARCHAR(255)等表示されます。内部的には、(255)などの精度指定は無視されています。
CREATE TABLE、CASTで指定できる型は、大文字、小文字、大文字小文字混じり、大文字小文字を意識せず使えます。
INTEGER型の最大値・最小値
INTEGERは、実際に設定された値によって格納されるバイト数が変化します。
- 1バイト,
- 2バイト,
- 3バイト,
- 4バイト,
- 6バイト,
- 8バイト
の符号ありの型に格納されます。
値によって範囲が異なります。
- -128から128の範囲の値は1バイト
- -32,768から32,767の数値は2バイト
- -8,388,608から8,388,607の数値は3バイト
- -2,147,483,648から2,147,483,647の範囲の値は4バイト
- -140,737,488,355,328から140,737,488,355,327の数値は6バイト
- 上記で収まらない数値は、-9,223,372,036,854,775,808から9,223,372,036,854,775,807(8バイト)
最大8バイトの数値まで格納できます。
PHPで扱える整数の最大値、最小値はPHP_INT_MAX、PHP_INT_MINでわかります。
1 2 3 4 5 |
<?php echo number_format( PHP_INT_SIZE ) . PHP_EOL; echo number_format( PHP_INT_MAX ) . PHP_EOL; echo number_format( PHP_INT_MIN ) . PHP_EOL; |
(PHP_INT_MINは、PHP7.0以降で有効な定数です。PHP7以前のPHP5では、Warningが発生します)
TEXT型の最長文字数
TEXT型の最長文字数は、1,000,000,000バイトです。
UTF-8は、1文字を1~6バイトで表現します。
UTF-16は、1文字を2バイト、または4バイトで表現します。
格納する文字コードや文字によって最大値は変動します。
最長文字数は166,666,666(UTF-8+6バイト文字列の場合)です。
TEXT型の最長文字数は、1,000,000,000バイトです。と記載しました。
この値は、SQLITE_MAX_LENGTHで宣言されています。
PHPコンパイル時に変更可能な値です。
SQLITE_MAX_LENGTHの設定を変更しない初期状態のままコンパイルしたPHPをご利用の場合、
TEXT型の最長文字数は、1,000,000,000バイトです。
SQLITE_MAX_LENGTHは、
PHPのソースコード(ext/sqlite3/libsqlite/sqlite3.c)で定義されています。
PHPコンパイル時に変更することが可能です。
レンタルサーバーなどでは、予めPHPが用意されています。SQLite3自体には、sqlite3_limit関数があります。
PHPは、sqlite3_limit関数が利用できません。
PHPのSQLITE_MAX_LENGTHの設定値を後から知るすべは用意されていません。
以下のようなPHPソースを動かして、SQLITE_MAX_LENGTHを地道に確認する方法で対応できます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
<?php function max_test($val){ try{ $ret = false; $db = new SQLite3(":memory:"); $db->exec('create table tbl( txt text )'); $stmt = $db->prepare('insert into tbl(txt) values (?)'); $stmt->bindParam(1, $text , SQLITE3_TEXT); $text = $val; $stmt->execute(); $stmt->close(); $insertedValue = $db->querySingle("select txt from tbl limit 1"); if( $insertedValue == $val ){ $ret = true; }else{ echo $ret . PHP_EOL; } $db->close(); return $ret; } catch(Exception $e){ echo $e->getMessage() ; return false; } } foreach( [1000,10000,100000,1000000,10000000,100000000,1000000000] as $length ){ $textval = str_repeat('a', $length); if( max_test($textval) == true ){ echo "OK : " .number_format( $length ) . PHP_EOL; }else{ echo "NG : " .number_format( $length ) . PHP_EOL; break; } $length ++; $textval = str_repeat('a', $length); if( max_test($textval) == true ){ }else{ echo "NG : " .number_format( $length ) . PHP_EOL; break; } } |
文字列の最大長に到達する前に、メモリの最大値を超える場合が多いです。
1 |
Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 100000001 bytes) in ... |
このようなFatal errorが発生した場合、phpが確保できるメモリの最大値を超えています。
PHPで以下のようにメモリリミットを変更可能です。
1 |
ini_set('memory_limit','256M'); |
BLOB型の最長文字数
BLOB型の最長文字数は、TEXTと同一です。
1,000,000,000バイトです。
real型の最大値・最小値
realは、8バイト(64bit)の倍精度浮動小数点数(IEEE)です。
PHPから扱う場合、最大値は、~1.8e308 です。
php.net 浮動小数点数