mysql tinyint，smallint，mediumint，int，bigint，decimal的使用

show create table table_int

mysql> show create table table_int;
CREATE TABLE `table_int` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `tinyint1` tinyint(4) unsigned DEFAULT NULL COMMENT '1个字节 0~255',
  `smallint2` smallint(6) unsigned DEFAULT NULL COMMENT '2个字节 0~65535',
  `mediumint3` mediumint(9) unsigned DEFAULT NULL COMMENT '3个字节 0~16777215',
  `int4` int(10) unsigned DEFAULT NULL COMMENT '4个字节 0~4294967295',
  `bigint8` bigint(20) unsigned DEFAULT NULL COMMENT '8个字节 0~18446744073709551615',
  `tinyint1$` tinyint(4) DEFAULT NULL COMMENT '-128~127',
  `smallint2$` smallint(6) DEFAULT NULL COMMENT '-32768~32767',
  `mediumint3$` mediumint(9) DEFAULT NULL COMMENT '-8388608~8388607',
  `int4$` int(11) DEFAULT NULL COMMENT '-2147483648~2147483647',
  `bigint8$` bigint(20) DEFAULT NULL COMMENT '-9223372036854775808~9223372036854775807',
  `xxx` tinyint(2) unsigned DEFAULT NULL COMMENT 'length并不会影响插入数据的大小，大小是由Type决定的。',
  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '删除状态，1是0否',
  `weight` decimal(3,2) unsigned NOT NULL DEFAULT '0.00' COMMENT 'decimal定义总长度为3，小数点前保留1位，小数点后保留2位，3.14可以，31.4不可以',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='1字节=8bit 【tinyint范围：[0, ( 2^(1*8) ) - 1]，int范围：[0, ( 2^(4*8) ) -1] 】';

desc table_int

mysql> desc table_int;
+-------------+------------------------+------+-----+---------+----------------+
| Field       | Type                   | Null | Key | Default | Extra          |
+-------------+------------------------+------+-----+---------+----------------+
| id          | int(11) unsigned       | NO   | PRI | NULL    | auto_increment |
| tinyint1    | tinyint(4) unsigned    | YES  |     | NULL    |                |
| smallint2   | smallint(6) unsigned   | YES  |     | NULL    |                |
| mediumint3  | mediumint(9) unsigned  | YES  |     | NULL    |                |
| int4        | int(10) unsigned       | YES  |     | NULL    |                |
| bigint8     | bigint(20) unsigned    | YES  |     | NULL    |                |
| tinyint1$   | tinyint(4)             | YES  |     | NULL    |                |
| smallint2$  | smallint(6)            | YES  |     | NULL    |                |
| mediumint3$ | mediumint(9)           | YES  |     | NULL    |                |
| int4$       | int(11)                | YES  |     | NULL    |                |
| bigint8$    | bigint(20)             | YES  |     | NULL    |                |
| xxx         | tinyint(2) unsigned    | YES  |     | NULL    |                |
| deleted     | bit(1)                 | NO   |     | b'0'    |                |
| weight      | decimal(3,2) unsigned  | NO   |     | 0.00    |                |
+-------------+------------------------+------+-----+---------+----------------+
14 rows in set (0.00 sec)

select * from table_int

mysql> select * from table_int;
+----+----------+-----------+------------+------------+----------------------+-----------+------------+-------------+-------------+----------------------+------+---------+--------+
| id | tinyint1 | smallint2 | mediumint3 | int4       | bigint8              | tinyint1$ | smallint2$ | mediumint3$ | int4$       | bigint8$             | xxx  | deleted | weight |
+----+----------+-----------+------------+------------+----------------------+-----------+------------+-------------+-------------+----------------------+------+---------+--------+
|  1 |      255 |     65535 |   16777215 | 4294967295 | 18446744073709551615 |       127 |     -32768 |     8388607 | -2147483648 | -9223372036854775808 |  200 |         |   3.14 |
+----+----------+-----------+------------+------------+----------------------+-----------+------------+-------------+-------------+----------------------+------+---------+--------+
1 row in set (0.00 sec)