mysql数据库整型字段设计例子

只要做开发的，肯定创建过表，表字段用什么类型，长度是多少等。感觉没什么要说的，可是归纳总结一下，还有东西可说的。

知道类型的范围值对我们有帮助的，例如，如果用tinyint，该字段的最大值不会超过255，把字段设置成tinyint(10),有符号的情况下，最大只能是127，表面上看好像是可以存入10位，而实际只能存入3位数。
根据要存数字大小，来选择不同的数据类型，例如，如果是标识位，一般情况设置为tinyint(1),最合适，存储小，又不会存不进去。

MariaDB [test]> CREATE TABLE `test` (
-> `big_test` bigint(20) DEFAULT 0,
-> `int_test` int(11) DEFAULT 0,
-> `medium_test` mediumint(8) DEFAULT 0,
-> `small_test` smallint(6) DEFAULT 0,
-> `tiny_test` tinyint(4) DEFAULT 0
-> ) ENGINE=myisam DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.09 sec)

MariaDB [test]> INSERT INTO test (big_test, int_test, medium_test, small_test, tiny_test) VALUES (9223372036854775807, 2147483647, 8388607, 32767, 127);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> select * from test;
+---------------------+------------+-------------+------------+-----------+
| big_test | int_test | medium_test | small_test | tiny_test |
+---------------------+------------+-------------+------------+-----------+
| 9223372036854775807 | 2147483647 | 8388607 | 32767 | 127 |
+---------------------+------------+-------------+------------+-----------+
1 row in set (0.00 sec)

MariaDB [test]> INSERT INTO test (big_test, int_test, medium_test, small_test, tiny_test) VALUES (9223372036854775807, 2147483647, 8388607, 32767, 128);
Query OK, 1 row affected, 1 warning (0.00 sec)   //128超过了，有符号tinyint的最大值，报了warning出来，但是还是入库了。

MariaDB [test]> select * from test;
+---------------------+------------+-------------+------------+-----------+
| big_test | int_test | medium_test | small_test | tiny_test |
+---------------------+------------+-------------+------------+-----------+
| 9223372036854775807 | 2147483647 | 8388607 | 32767 | 127 |
| 9223372036854775807 | 2147483647 | 8388607 | 32767 | 127 |
+---------------------+------------+-------------+------------+-----------+
2 rows in set (0.00 sec)

MariaDB [test]> ALTER TABLE `test` CHANGE `tiny_test` `tiny_test` TINYINT( 10 ) NULL DEFAULT Ɔ'
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0   //将长度改为10，没报错

MariaDB [test]> INSERT INTO test (big_test, int_test, medium_test, small_test, tiny_test) VALUES (9223372036854775807, 2147483647, 8388607, 32767, 128);
Query OK, 1 row affected, 1 warning (0.00 sec)   //在插入数据，一样报warning，但还是入库

MariaDB [test]> select * from test;
+---------------------+------------+-------------+------------+-----------+
| big_test | int_test | medium_test | small_test | tiny_test |
+---------------------+------------+-------------+------------+-----------+
| 9223372036854775807 | 2147483647 | 8388607 | 32767 | 127 |
| 9223372036854775807 | 2147483647 | 8388607 | 32767 | 127 |
| 9223372036854775807 | 2147483647 | 8388607 | 32767 | 127 |
+---------------------+------------+-------------+------------+-----------+
3 rows in set (0.00 sec)