データベースにはいろんな型があります。
ちゃんと意図した値が入ってくるなら問題はないのだけど、意図しない値が入ることもある。
そしてNULLとか空文字が入った時どうなるのか分からないものがあったので、この際一覧にしてみた。
今回はいろんな型にNULLと空文字を入れてみた結果のまとめです。
環境はSQL Serverです。
そして普段使わない型がいっぱいあって、初めて知った型もあった。
それでは早速SQLです。
DECLARE @charTest char(10);
DECLARE @varcharTest varchar(10);
DECLARE @ncharTest nchar(10);
DECLARE @nvarcharTest nvarchar(10);
DECLARE @intTest int;
DECLARE @bigintTest bigint;
DECLARE @smallintTest smallint;
DECLARE @tinyintTest tinyint;
DECLARE @decimalTest decimal;
DECLARE @floatTest float;
DECLARE @realTest real;
DECLARE @bitTest bit;
DECLARE @numericTest numeric;
DECLARE @dateTest date;
DECLARE @datetimeTest datetime;
DECLARE @datetime2Test datetime2;
DECLARE @smalldatetimeTest smalldatetime;
DECLARE @moneyTest money;
DECLARE @smallmoneyTest smallmoney;
DECLARE @binaryTest binary;
DECLARE @varbinaryTest varbinary;
DECLARE @sql_variantTest sql_variant;
DECLARE @timestampTest timestamp;
DECLARE @uniqueidentifierTest uniqueidentifier;
DECLARE @cursorTest cursor;
DECLARE @xmlTest xml;
SET @charTest = NULL;
SET @varcharTest = NULL;
SET @ncharTest = NULL;
SET @nvarcharTest = NULL;
SET @intTest = NULL;
SET @bigintTest = NULL;
SET @smallintTest = NULL;
SET @tinyintTest = NULL;
SET @decimalTest = NULL;
SET @floatTest = NULL;
SET @realTest = NULL;
SET @bitTest = NULL;
SET @numericTest = NULL;
SET @dateTest = NULL;
SET @datetimeTest = NULL;
SET @datetime2Test = NULL;
SET @smalldatetimeTest = NULL;
SET @moneyTest = NULL;
SET @smallmoneyTest = NULL;
SET @binaryTest = NULL;
SET @varbinaryTest = NULL;
SET @sql_variantTest = NULL;
SET @timestampTest = NULL;
SET @uniqueidentifierTest = NULL;
SET @xmlTest = NULL;
SELECT '--NULL--'
SELECT
@charTest AS charTest
,@varcharTest AS varcharTest
,@ncharTest AS ncharTest
,@nvarcharTest AS nvarcharTest
,@intTest AS intTest
,@bigintTest AS bigintTest
,@smallintTest AS smallintTest
,@tinyintTest AS tinyintTest
,@decimalTest AS decimalTest
,@floatTest AS floatTest
,@realTest AS realTest
,@bitTest AS bitTest
,@numericTest AS numericTest
,@dateTest AS dateTest
,@datetimeTest AS datetimeTest
;
SELECT
@datetime2Test AS datetime2Test
,@smalldatetimeTest AS smalldatetimeTest
,@moneyTest AS moneyTest
,@smallmoneyTest AS smallmoneyTest
,@binaryTest AS binaryTest
,@varbinaryTest AS varbinaryTest
,@sql_variantTest AS sql_variantTest
,@timestampTest AS timestampTest
,@uniqueidentifierTest AS uniqueidentifierTest
,@xmlTest AS xmlTest
;
SET @charTest = '';
SET @varcharTest = '';
SET @ncharTest = '';
SET @nvarcharTest = '';
SET @intTest = '';
SET @bigintTest = '';
SET @smallintTest = '';
SET @tinyintTest = '';
SET @floatTest = '';
SET @realTest = '';
SET @bitTest = '';
SET @dateTest = '';
SET @datetimeTest = '';
SET @datetime2Test = '';
SET @smalldatetimeTest = '';
SET @moneyTest = '';
SET @smallmoneyTest = '';
SET @xmlTest = '';
SELECT '--空白--'
SELECT
@charTest AS charTest
,@varcharTest AS varcharTest
,@ncharTest AS ncharTest
,@nvarcharTest AS nvarcharTest
,@intTest AS intTest
,@bigintTest AS bigintTest
,@smallintTest AS smallintTest
,@tinyintTest AS tinyintTest
,@floatTest AS floatTest
,@realTest AS realTest
,@bitTest AS bitTest
,@dateTest AS dateTest
,@datetimeTest AS datetimeTest
;
SELECT
@datetime2Test AS datetime2Test
,@smalldatetimeTest AS smalldatetimeTest
,@moneyTest AS moneyTest
,@smallmoneyTest AS smallmoneyTest
,@xmlTest AS xmlTest
;
実行結果(NULL)
NULLはcursorだけ格納できなかった。
そして、timestampはNULLを格納してもNULLと表示されなかった。
実行結果(空文字)
空文字は
decimal
numeric
binary
varbinary
sql_variant
timestamp
uniqueidentifier
cursor
が格納できなかった。
そして、空白が格納できても数値系は0になる。
日付系は1990〜になる。
こういうのもデータベースごとに仕様が違うだろうから意識しないとな。