MySQLデータ型の詳細
2022-01-06 11:04:29
1. 数値型
1.1、数値型分類
-
厳密には数値型(
INTEGERSMALLINT,DECIMALandNUMERIC)Approximate numeric data type (FLOAT,REALandDOUBLE PRECISION)
Numeric types in MySQL.
<テーブル
整数型
バイト
最小値
最大値
TINYINT
1
符号付き -128
符号なし0
符号付き 127
符号なし 255
SMALLINT
2
符号付-32768
符号なし0
符号付き 32767
符号なし 65535
メディアミント
3
符号付-8388608
符号なし0
符号付き 8388607
符号なし 1677215
INT, INTEGER
4
符号付-2147483648
符号なし0
符号付き 2147483647
符号なし 4294967295
BIGINT
8
with symbol-9223372036854775808
無記号0
符号付き 9223372036854775807
符号なし 18446744073709551615
浮動小数点型
バイト
最小
最大値
フロート
4
±1.175494351E-38
±3.402823466E+38
ダブル（DOUBLE
8
±2.2250738585072014E-308
±1.7976931348623157E+308
固定小数点型
バイト
説明
DEC(M,D)です。
DECIMAL(M,D)
M+2
最大範囲はDOUBLEと同じで、あるDECIMALに対して有効な値の範囲はMとDで示されます
を決定する
ビットタイプ
バイト
最小値
最大
ビット(M)
1～8
ビット(1)
BIT(64)
1.1.1, Floating-point numbers
If the floating point number is not written with precision and scale, it will be displayed according to the actual precision value, if there is precision and scale, the rounded result will be inserted automatically and the system will not report an error; if the fixed point number is not written with precision and scale, it will be displayed according to the default value
decimal(10,0)
and if the data exceeds the precision and scale values, the system will report an error.
1.1.2. Bit types
BIT (bit) type: range from 1-64, if not written, the default is 1 bit, for this field, direct use of the select command will not see the results, you can use bin() (displayed in binary format) or hex() (displayed in hexadecimal format) function to read
Example.
mysql> desc t2;
+-------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id | bit(1) | YES | | NULL | |
+-------+--------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into t2 values(1);
Query OK, 1 row affected (0.05 sec)
mysql> select * from t2;
+------+
| id |
+------+
| id
+------+
1 row in set (0.00 sec)
mysql> select bin(id),hex(id) from t2;
+---------+---------+
| bin(id) | hex(id) |
+---------+---------+
1 | 1 | 1 |
+---------+---------+
1 row in set (0.03 sec)
bit
When type data is inserted, the value is first converted to binary, and if allowed, the insertion is performed; if the number of bits is less than the actual defined number of bits, the insertion fails.
Example.
If you insert 2 in that table just now, the actual conversion to binary is 10, which is beyond the
bit(1)
If the actual number of bits defined in the table is 10, it will report an exception, define the id as bit(2), insert it, and it will succeed
mysql> insert into t2 values(2);
ERROR 1406 (22001): Data too long for column 'id' at row 1
mysql> alter table t2 modify id bit(2);
Query OK, 1 row affected (0.67 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into t2 values(2);
Query OK, 1 row affected (0.03 sec)
mysql> select bin(id),hex(id) from t2;
+ ---------+---------+
| bin(id) | hex(id) |
+---------+---------+
| 1 | 1 | 10
| 10 | 2 |
+---------+---------+
2 rows in set (0.00 sec)
1.1.3, Time and date type
1.1.3, Time and date types
1.1.1, Floating-point numbers
If the floating point number is not written with precision and scale, it will be displayed according to the actual precision value, if there is precision and scale, the rounded result will be inserted automatically and the system will not report an error; if the fixed point number is not written with precision and scale, it will be displayed according to the default value
decimal(10,0)
and if the data exceeds the precision and scale values, the system will report an error.
1.1.2. Bit types
BIT (bit) type: range from 1-64, if not written, the default is 1 bit, for this field, direct use of the select command will not see the results, you can use bin() (displayed in binary format) or hex() (displayed in hexadecimal format) function to read
Example.
mysql> desc t2;
+-------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id | bit(1) | YES | | NULL | |
+-------+--------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into t2 values(1);
Query OK, 1 row affected (0.05 sec)
mysql> select * from t2;
+------+
| id |
+------+
| id
+------+
1 row in set (0.00 sec)
mysql> select bin(id),hex(id) from t2;
+---------+---------+
| bin(id) | hex(id) |
+---------+---------+
1 | 1 | 1 |
+---------+---------+
1 row in set (0.03 sec)
bit
When type data is inserted, the value is first converted to binary, and if allowed, the insertion is performed; if the number of bits is less than the actual defined number of bits, the insertion fails.
Example.
If you insert 2 in that table just now, the actual conversion to binary is 10, which is beyond the
bit(1)
If the actual number of bits defined in the table is 10, it will report an exception, define the id as bit(2), insert it, and it will succeed
mysql> insert into t2 values(2);
ERROR 1406 (22001): Data too long for column 'id' at row 1
mysql> alter table t2 modify id bit(2);
Query OK, 1 row affected (0.67 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into t2 values(2);
Query OK, 1 row affected (0.03 sec)
mysql> select bin(id),hex(id) from t2;
+ ---------+---------+
| bin(id) | hex(id) |
+---------+---------+
| 1 | 1 | 10
| 10 | 2 |
+---------+---------+
2 rows in set (0.00 sec)
1.1.3, Time and date type
1.1.3, Time and date types
DATE
: used to indicate the month, day and year
DATETIME
: used to indicate the year, month, day, hour, minute, and second (supported insertion formats: 2007-9-3 12:10:10', '2007/9/3 12+10+10', '20070903121010', 20070903121010, etc.)
TIME
: Only for hours, minutes and seconds
TIMESTAMP
: often insert or update the date to the current system time
YEAR
: indicates the year
Example 1.
Create a time table (dt) with date, time, and datetime fields, and insert values to see the results
mysql> create table dt(d date,t time,dt datetime);
Query OK, 0 rows affected (0.23 sec)
mysql> insert into dt values(now(),now(),now());
Query OK, 1 row affected, 1 warning (0.05 sec)
mysql> select * from dt;
+ ------------+----------+---------------------+
| d | t | dt |
+------------+----------+---------------------+
| 2021-05-13 | 10:14:07 | 2021-05-13 10:14:07 |
+------------+----------+---------------------+
1 row in set (0.00 sec)
Example 2.
Create a test table t with field id1 of type TIMESTAMP, insert the null value, and display
mysql> create table t(id1 timestamp);
Query OK, 0 rows affected (0.22 sec)
mysql> insert into t values(null);
Query OK, 1 row affected (0.05 sec)
mysql> select * from t;
+ ---------------------+
| id1
+---------------------+
| 2021-05-13 10:18:05 |
+---------------------+
1 row in set (0.00 sec)
You can see that the system has automatically created a default value for id1
CURRENT_TIMESTAMP
(system date). (Note that the
MySQL
gives only the first
TIMESTAMP
field to set the default value to the system date, and if there is a second
TIMESTAMP
type, then the default value is set to a value of 0)
Example 3.
Explain as illustrated above
mysql> alter table t add column id2 timestamp;
Query OK, 0 rows affected (0.48 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t \G;
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`id2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
ERROR:
No query specified
1.1.4, String type
CHAR
: fixed length, trailing spaces are removed when retrieving
VARCHAR
: variable-length string, these spaces are preserved when retrieving
Example.
mysql> create table varc(v varchar(4),c char(4));
Query OK, 0 rows affected (0.20 sec)
mysql> insert into varc values('abc ','abc ');
Query OK, 1 row affected (0.03 sec)
mysql> select length(v),length(c) from varc;
+ -----------+-----------+
| length(v) | length(c) |
+-----------+-----------+
| 4 | 3 |
+-----------+-----------+
1 row in set (0.01 sec)
mysql> select concat(v,'+'),concat(c,'+') from varc;
+---------------+---------------+
| concat(v,'+') | concat(c,'+') | concat(v,'+')
+---------------+---------------+
| abc + | abc+ |
+---------------+---------------+
1 row in set (0.00 sec)
BINARY
: similar to
char
but it is a binary string
VARBINARY
: similar to
varchar
which stores the binary string
Example.
mysql> create table bina(c binary(3));
Query OK, 0 rows affected (0.22 sec)
mysql> insert into t set c='a';
ERROR 1054 (42S22): Unknown column 'c' in 'field list'
mysql> insert into bina set c='a';
Query OK, 1 row affected (0.05 sec)
mysql> select *,hex(c),c='a',c='a\0',c='a\0\0' from bina;
+------+--------+-------+---------+-----------+
| c | hex(c) | c='a' | c='a\0' | c='a\0\0' | c='a\0\0'
+------+--------+-------+---------+-----------+
| a | 610000 | 0 | 0 | 1 |
+------+--------+-------+---------+-----------+
1 row in set (0.00 sec)
You can see that when saving the
BINARY
value, the specified field definition length is reached by padding the end of the value with "0x00" (zero bytes). As seen in the above example, for a
BINARY(3)
column, 'a' becomes 'a\0\0' when inserted
1.1.5, ENUM type
ENUM
: 1 byte for enumeration of 1 to 255 members; 2 bytes for enumeration of 255 to 65535 members.
Example.
mysql> create table gend(gender enum('M','F'));
Query OK, 0 rows affected (0.20 sec)
mysql> insert into gend values('M'),('F'),('1'),(null);
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from gend;
+--------+
| gender
+--------+
|gender
|M
| M
| NULL |
+--------+
4 rows in set (0.00 sec)
As you can see, the insert value of the enumeration type is case insensitive, with the initial subscript starting at 1; it is converted to uppercase when queried, and can also be inserted as null
1.1.6, SET type
SET
: String object, which can contain 64 members, with different members and different storage
The set of 1 to 8 members, occupying 1 byte.
The set of 9 to 16 members, occupying 2 bytes.
The set of members from 17 to 24, occupying 3 bytes.
The set of members from 25 to 32, occupying 4 bytes.
The set of members from 33 to 64, occupying 8 bytes.
Set
and
EMUN
The difference is that Set can select more than one member at a time, while ENUM can only select one
Example.
mysql> create table st(col set('a','b','c','d'));
Query OK, 0 rows affected (0.20 sec)
mysql> insert into st values('a,b'),('a,d,a'),('a,c'),('a');
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into st values('f');
ERROR 1265 (01000): Data truncated for column 'col' at row 1
mysql> insert into st values(null);
Query OK, 1 row affected (0.03 sec)
mysql> select * from st;
+ ------+
| col |
+------+
| a,b |
| a,d |
| a,c |
| a |
| NULL |
+------+
5 rows in set (0.00 sec)
As you can see, it is possible to insert multiple members into a set, and it is also possible to insert null, and an exception will be thrown when inserting a non-existent defined column
<テーブル
日付と時刻の種類
バイト数
最小値
最大値
日付
4
1000-01-01
9999-12-31
DATETIME
8
1000-01-01 00:00:00
9999-12-31 23:59:59
タイムスタンプ
4
19700101080001
2038年のある時点で
時間
3
-838:59:59
838:59:59
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
[...]
関連
-
MySQLのWhereの使用方法について説明します。
-
MySQL サービスとデータベース管理
-
MySQL XAが分散型トランザクションを実装する方法を1記事にまとめました。
-
[解決済み] datetimeの挿入時に文字列から日付や時刻を変換すると、変換に失敗する
-
[解決済み] 親の行を削除または更新できない：外部キー制約が失敗する
-
[解決済み] ユニークなテーブル/エイリアスではない
-
[解決済み] [GROUP BY句に含まれるか、集約関数で使用される必要があります。
-
MySQLにおけるvarchar型とchar型の違い
-
群関数解の無効な使用
-
mysql がエラーを報告します。不明な文字セットです。'utf8mb4'
最新
-
nginxです。[emerg] 0.0.0.0:80 への bind() に失敗しました (98: アドレスは既に使用中です)
-
htmlページでギリシャ文字を使うには
-
ピュアhtml+cssでの要素読み込み効果
-
純粋なhtml + cssで五輪を実現するサンプルコード
-
ナビゲーションバー・ドロップダウンメニューのHTML+CSSサンプルコード
-
タイピング効果を実現するピュアhtml+css
-
htmlの選択ボックスのプレースホルダー作成に関する質問
-
html css3 伸縮しない 画像表示効果
-
トップナビゲーションバーメニュー作成用HTML+CSS
-
html+css 実装 サイバーパンク風ボタン
おすすめ
-
MySQL Innodb インデックスメカニズム詳細解説
-
MySQLにおけるorder byの使用方法の詳細
-
MySQLのLike演算子に関する詳細
-
MySql認証ベースのvsftpd仮想ユーザー
-
MySQLによる既存テーブルのパーティショニングの実装
-
面接では選択式で聞かれましたが......。.for updateはテーブルをロックするか、行をロックするか？
-
[解決済み】MySQL - オペランドは1つのカラムを含む必要があります。
-
[解決済み】MySQLで「すべての派生テーブルは独自のエイリアスを持つ必要があります」というエラーは何ですか？
-
[解決済み】ValueError: 値の長さがインデックスの長さと一致しない｜Pandas DataFrame.unique()
-
SQLステートメントエラーです。オペランドには 1 つのカラムを含める必要があります [括弧を追加せずに複数のフィールドをクエリする