Database Design field type setting experience

Organize the past experiences in development. Post it today, sort it out, and make a memo. Tinyint is-128 to 128. When the attribute is set to unsigned. The maximum value is 255. Now you know why you need to set it to the unsigned attribute. It was originally intended to maximize the use of the given storage space. If this parameter is not set. So assume that all your values are

Organize the past experiences in development. Post it today, sort it out, and make a memo. Tinyint is-128 to 128. When the attribute is set to unsigned. The maximum value is 255. Now you know why you need to set it to the unsigned attribute. It was originally intended to maximize the use of the given storage space. If this parameter is not set. So assume that all your values are

Organize the past experiences in development. Post it today, sort it out, and make a memo.

Tinyint is-128 to 128. When the attribute is set to unsigned. The maximum value is 255. Now you know why you need to set it to the unsigned attribute. It was originally intended to maximize the use of the given storage space. If this parameter is not set. If your values are all positive. So the 128-one hundred numbers are a waste.
Tinyint is automatically set to tinyint (3)
When smallint is not set to unsigned, it also looks like more than 30 thousand.


Tinytext is 255 bytes. Tinytext is equivalent to varchar. It is easy to understand this type.

Phpmyadmin of int type sets int (10) by default)

Concept correction: We thought that 10 represents the number of digits. Until 1101061021496 is saved, the value of the result in the field is changed to 4294967295. Read the MySQL manual:

The display width does not limit the range of values that can be saved in the column, nor the display of values that exceed the specified width of the column.
Int range:-2147483648 to 2147483647. There are exactly 10 digits, which are billions of digits. Database Design Experience: The order value is very large. Not sure. If it reaches 10 digits, it is better to use the varchar type. Fangwei does not use int, but is of the varchar type.



I also learned from the above: if the values stored in the database are the same. That is, no matter whether I am 1101061021496 or 1101061021569, the result is changed to a fixed value, such as 4294967295. Check whether the field range of the database is correct. This problem has occurred several times. That is, you have no idea. This wastes a lot of time.



If the column in the mysql table contains null, the field is not null. That is, the index is invalid. For all fields that will be indexed in the future, it is necessary to set the field attribute to not null.
If you want to save NULL, set it manually instead of setting it as the default value.



Considering that this field will be searched as a query keyword in the form of like in the future. Define this field as an index. In this way, like queries are faster.
Now I finally learned from the foreign author's book that I should ask myself what data I will query before designing a database. With this in mind, there will be any query needs in the future. The structure can be adapted.


//////////////////////////////////////// //////////////////////////////////////// //////////////
//////////////////////////////////////// //////////////////////////////////////// //////////////

Considerations for designing large-volume website databases, member table shards, or database shards:

Do not set the primary key to auto-increment. The consequence of setting it to auto-increment is that it cannot be separated from different mysql database servers in the future. For example, because the id number is auto-incrementing, the user numbers in both databases may all be 10005.

However, the mysql primary key is automatically set to auto-incrementing. You can use another field as the identifier. Instead of the auto-incrementing ID. Method: Add a field as the row identifier. Specific design: A table has two fields. One is id as the primary key, auto-incrementing, and the other is uid, which serves as the user's id.

In program judgment, uid is used as the basis for user judgment. Instead of using the id Primary Key as the judgment basis (program errors make changes much easier than database design errors. Because your data has been imported into the database. It is difficult to modify it)
//////////////////////////////////////// //////////////////////////////////////// //////////////
//////////////////////////////////////// //////////////////////////////////////// //////////////