Mysql 基礎資料型別(無時間相關型別)

資料型別無時間相關型別

--zerofill不滿足位數的用0填充

mysql> use yws
Database changed
mysql> create table t1(id1 int,id2 int(5));
Query OK, 0 rows affected (0.11 sec)


mysql> insert into t1 values(1,1);
Query OK, 1 row affected (0.06 sec)


mysql> commit;
Query OK, 0 rows affected (0.00 sec)


mysql> alter table t1 modify id1 int zerofill;
Query OK, 1 row affected (0.32 sec)
Records: 1  Duplicates: 0  Warnings: 0




mysql> alter table t1 modify id2 int zerofill;
Query OK, 1 row affected (0.23 sec)
Records: 1  Duplicates: 0  Warnings: 0


mysql> select * from t1;
+------------+------------+
| id1        | id2        |
+------------+------------+
| 0000000001 | 0000000001 |
+------------+------------+
1 row in set (0.00 sec)

increment型別
mysql> create table a1(id int auto_increment not null primary key);
Query OK, 0 rows affected (0.11 sec)



mysql> alter table a1 add column name varchar(20);
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> insert into a1 (name) values ('yws');
Query OK, 1 row affected (0.07 sec)


mysql> commit;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from a1;
+----+------+
| id | name |
+----+------+
|  1 | yws  |
+----+------+
1 row in set (0.00 sec)


mysql> insert into a1 (name) values ('yws');
Query OK, 1 row affected (0.05 sec)


mysql> commit;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from a1;
+----+------+
| id | name |
+----+------+
|  1 | yws  |
|  2 | yws  |
+----+------+
2 rows in set (0.00 sec)

float double decimal型別

mysql> create table a2(id1 float(5,2),id2 double(5,2),id3 decimal(5,2));
Query OK, 0 rows affected (0.09 sec)



mysql> insert into a2 values(1.23,1.23,1.23);
Query OK, 1 row affected (0.07 sec)


mysql> select * from a2;
+------+------+------+
| id1  | id2  | id3  |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
+------+------+------+
1 row in set (0.00 sec)


mysql> commit;
Query OK, 0 rows affected (0.00 sec)


mysql> insert into a2 values(1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.03 sec)




mysql> select * from a2;
+------+------+------+
| id1  | id2  | id3  |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
+------+------+------+
2 rows in set (0.00 sec)


mysql> alter table a2 modify column id1 float;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> alter table a2 modify column id2 double;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> alter table a2 modify column id3 decimal;
Query OK, 2 rows affected, 2 warnings (0.24 sec)
Records: 2  Duplicates: 0  Warnings: 2


mysql> insert into a2 values(1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.06 sec)


mysql> select * from a2;
+-------+-------+------+
| id1   | id2   | id3  |
+-------+-------+------+
|  1.23 |  1.23 |    1 |
|  1.23 |  1.23 |    1 |
| 1.234 | 1.234 |    1 |
+-------+-------+------+
3 rows in set (0.00 sec)


mysql> commit;
Query OK, 0 rows affected (0.00 sec)

bit資料型別

mysql> create table a3 (id bit);
Query OK, 0 rows affected (0.09 sec)


mysql> desc a3;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id    | bit(1) | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
1 row in set (0.01 sec)


mysql> insert into a3 values(11);
ERROR 1406 (22001): Data too long for column 'id' at row 1
mysql> insert into a3 values(1);
Query OK, 1 row affected (0.05 sec)


mysql> select * from a3;
+------+
| id   |
+------+
|    |
+------+
1 row in set (0.00 sec)


mysql> select bin(id),hex(id) from a3;
+---------+---------+
| bin(id) | hex(id) |
+---------+---------+
| 1       | 1       |
+---------+---------+
1 row in set (0.04 sec)


mysql>