java - bigint(20)、smallint(5)

Question

CREATE TABLE `idc_logistics_assign_rules` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `sp_id` bigint(20) unsigned NOT NULL COMMENT '外键关联表ID',
  `creator` varchar(255) NOT NULL COMMENT '创建人工号',
  `gmt_create` datetime NOT NULL COMMENT '创建时间',
  `modifier` varchar(255) NOT NULL COMMENT '修改人工号',
  `gmt_modified` datetime NOT NULL COMMENT '修改时间',
  `rule_name` varchar(255) NOT NULL COMMENT '规则名称',
  `rule_json_val` varchar(4096) NOT NULL COMMENT '规则JSON字符串',
  `rule_content` varchar(4096) NOT NULL COMMENT '规则中文描述',
  `type` varchar(128) NOT NULL COMMENT '类型（同机房、同城、区域内、区域外、其他）',
  `rule_lable` varchar(256) NOT NULL COMMENT '标签',
  `is_valid` char(1) NOT NULL COMMENT '是否有效（y/n），默认n',
  `is_deleted` char(1) NOT NULL COMMENT '是否删除',
  `ordering` smallint(5) unsigned NOT NULL COMMENT '排序字段',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_ordering` (`ordering`),
  KEY `idx_rule_content` (`rule_content`(255))
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='表名';

上面是建表的SQL语句，对于其中的数据类型有不明白的地方，如下：
1.smallint(5),我看了smallint本来的范围是：

A smallint is between -32768 and 32767 signed, or 0 and 65535 unsigned.

但是加了smallint(5)之后，对它的范围并没有影响，那么加这个有什么用呢？。假如我不指定括号中的位数呢？它的默认值是要取什么值呢？
2.同理varchar(255)是表示255个字节么？如果要存中文的话，用utf8编码，算上标点符号，大概能存多少个中文汉字呢？
3.另外还有datetime这种数据类型，一般并不指定有效位数的。那么如果我要精确到秒的、精确的分的、精确到月的，数据库中是不能直接这么存的么？只能存一个完整的时间(存一个以1970开始的long型方便),然后查的时候，可以用Mysql提供的函数来过滤？？

======================================================================
在列中使用zerofill，如插入int(4),你插入1，显示0001，你插入55555，显示也是55555，插入负数显示为0000，因为mysql自动增加UNSIGNED属性 UNSIGNED不能为负数,当你插入负数时就显示0, 多操作就能理解 希望采纳

Answer 1

1 smallint, int, there is no difference if you set the length or not.
2 utf8 255/3 gbk 255/2
3 The timestamp stored in general time, FROM_UNIXTIME(unix_timestamp,format) comes with mysql The timestamp to time can meet your needs

Answer 2

• Similar to int, the byte brackets behind it have no effect;

• Characters like char are characters, and the length of the characters is limited in brackets

Answer 3

1-Neat and good-looking
2-Probably 255/3, I haven’t studied it too carefully
3-Usually they are bigint timestamps or timestamp/datetime types. PHP can format the output when outputting, and there is no need for mysql. Side processing.

Answer 4

1.The default value of smallint is 6 because of the sign bit. It has no effect on the range. You can display it how you want. But you can see the difference by adding ZEROFILL after SMALLINT(3). 2.varchar(255) represents 255 characters, not bytes. You can do this Experiment, but some people say that the old version is bytes. If it is a character, you can store 255 Chinese characters, and the bytes are 255/3, because one Chinese character is one character, and one character is counted as 3 bytes in UTF8.
3. This is mainly because you divide the time in the background. You can add a few more fields of year, month, day, hour, minute and second. Of course, mysql also has functions that can be processed, such as: HOUR(time), SECOND(time), MONTH(time)

Answer 5

1. As others have said, the numbers in the brackets are just fill-in numbers and have no effect on the value range represented by the field. The difference will only be reflected when you set the field to zerofill. ;
2. Just do a small test on this yourself, I will talk about it below;
3. If the amount of data is large and the date field is one of the query conditions, it will be better to use timestamp storage, otherwise it will be used as needed. date, datetime和timestamp，又或者按需增加年(smallint)、月(tinyint)、日(tinyint) and other fields;

Regarding questions 1 and 2, I created a new table and did a test, as follows:

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(4) unsigned zerofill DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

INSERT INTO test
(a, b)
VALUES
(1, '1234567890'),
(11, '零一二三四五六七八九');

Result:

mysql> select * from test;
+----+------+--------------------------------+
| id | a    | b                              |
+----+------+--------------------------------+
|  1 | 0001 | 1234567890                     |
|  2 | 0011 | 一二三四五六七八九零           |
+----+------+--------------------------------+
2 rows in set (0.01 sec)

But the following behavior will depend on the settings of sql_mode, for example:
Write a record in which the length of the b value is 11,

INSERT INTO test
(b)
VALUES
('一二三四五六七八九零一');

I can write to a local database without error, but the stored data is 1234567890一二三四五六七八九零，也就是说会把超过10位的数据抹掉；
但在我本地的另一个数据库却发现报错了，报Data too long for column 'b' at row 1。
通过SELECT @@sql_mode;发现，后者多了一个STRICT_TRANS_TABLES, which means that more than 10 digits will be The data was erased;

But I found an error in another local database, reporting Data too long for column 'b' at row 1.

Through SELECT @@sql_mode;, we found that the latter has an additional STRICT_TRANS_TABLES, causing an error to be reported when trying to write data that does not meet the ddl definition.

🎜Reference: https://dev.mysql.com/doc/ref...🎜