Mysql value data type

Mysql numeric data type MySQL numeric data types can be roughly divided into two categories, one is an integer, the other is a floating point or decimal. Many different subtypes are available for each of these classes. Each subtype supports data of different sizes. MySQL allows us to specify whether the values in the value field are positive or negative or fill with zero. The table lists various numeric types, their permitted ranges, and occupied memory space. Type size range (Signed) range (unsigned) purpose TINYINT 1 byte (-128,127) (0,255) small integer SMALLINT 2 bytes (-32 768,32 767) (535) large integer mediumint 3 bytes (-8 388 608 388 607) (777 215 147) large integer int or INTEGER 4 bytes (-2 483 147 483 647, 2) (294 967 295 233) Big integer BIGINT 8 bytes (-9 372 854 036 775 223 372 854 775 807 446 744 709 551 615) FLOAT 4 bytes (-3.402 823 466 E + 38, 1.175 494 351 E-38), 0, (1.175 494 351 E-38, 3.402 823 466 E + 38) 0, (351 1.175 494 E-38, 351 3.402 E + 38) single precision floating point value DOUBLE 8 bytes (1.797 693 134 862 315 7 E + 308, 2.225 073 858 507 201 4 E-308), 0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E + 308) 0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E + 308) Double Precision Floating Point DECIMAL pair DECIMAL (M, d) If M> D, is M + 2 otherwise D + 2 depends on M and D values depend on M and D values small value INT type supported in MySQL five major integer types are TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT. These types are largely the same, and only the sizes of the stored values are different. MySQL expands the SQL standard in the form of an optional display width indicator, so that when you retrieve a value from the database, you can extend this value to the specified length. For example, if you specify the type of a field as INT (6), you can ensure that the values containing less than 6 digits can be automatically filled with spaces when retrieved from the database. It should be noted that using a width indicator does not affect the field size and the range of values it can store. In case we need to store a number that exceeds the permitted range for a field, MySQL will truncate the end that is closest to the permitted range before storing it. Another special note is that MySQL will automatically change the value to 0 before the table is inserted with an unspecified value. The UNSIGNED modifier specifies that the field only saves positive values. Because you do not need to save the positive and negative symbols of numbers, you can reserve a space of about a bit during the storage period. This increases the range of values that this field can store. The ZEROFILL modifier specifies that 0 (not a space) can be used to truly complement the output value. This modifier can be used to prevent negative values from being stored in the MySQL database. FLOAT, DOUBLE, and DECIMAL types. MySQL supports FLOAT, DOUBLE, and DECIMAL types. FLOAT values are used to represent single-precision floating point values, while DOUBLE values are used to represent DOUBLE-precision floating point values. Like integers, these types also have additional parameters: a display width indicator and a decimal point indicator. For example, the FLOAT () statement specifies that the displayed value is no more than 7 digits, and the decimal point is followed by three digits. MySQL will automatically round the number of digits after the decimal point to the value closest to it and then insert it. The DECIMAL data type is used in computation with very high precision requirements. This type allows you to specify the precision and counting method of a value as the selection parameter. The precision here refers to the total number of valid numbers saved for this value, and the counting method indicates the number of digits after the decimal point. For example, the DECIMAL () statement specifies that the stored value cannot exceed 7 digits, and the DECIMAL point cannot exceed 3 digits. Ignoring the DECIMAL data type precision and counting method modifier will make MySQL database set the precision of all fields marked as this data type to 10, and the calculation method to 0. The UNSIGNED and ZEROFILL modifiers can also be used by FLOAT, DOUBLE, and DECIMAL data types. And the effect is the same as that of the INT data type. String type MySQL provides eight basic string types that can be stored from a simple character to a huge text block or binary string data. Type size usage CHAR 0-255 byte fixed length string VARCHAR 0-255 BYTE Variable Length string TINYBLOB 0-255 byte binary string of no more than 255 characters TINYTEXT 0-255 byte short text string BLOB 0-65 535 byte binary form long TEXT data TEXT 0-65 535 bytes long TEXT data MEDIUMBLOB 0-16 777 215 777 bytes moderate-length TEXT data in binary format MEDIUMTEXT 0-16 215 bytes moderate-length TEXT data LOGNGBLOB 0- 4 294 967 295 bytes extremely large text data in binary form LONGTEXT 0-4 294 967 295 bytes extremely large text data CHAR and varchar char types are used for fixed-length strings, A size modifier must be defined in parentheses. The size modifier ranges from 0 to 255. Values greater than the specified length are truncated, while values smaller than the specified length are filled with spaces. The CHAR type can use the BINARY modifier. When used for comparison operations, this modifier enables CHAR to be involved in operations in binary mode, rather than the traditional case-sensitive mode. A variant of the CHAR type is the VARCHAR type. It is a variable-length string type and must also contain an indicator ranging from 0 to 255. The difference between CHAR and VARCHGAR lies in the way the MuSQL database processes this indicator: CHAR regards this size as the value size and uses spaces to fill it up without being short. The VARCHAR type regards it as the maximum value and only uses the length required to store the string (an additional byte is added to store the length of the string itself) to store the value. Therefore, the VARCHAR type shorter than the indicator length will not be filled by spaces, but the value longer than the indicator will still be truncated. Because the VARCHAR type can dynamically change the length of the storage value according to the actual content, using the VARCHAR type can greatly save disk space and improve storage efficiency when you cannot determine the number of characters required by the field. The VARCHAR type is identical to the CHAR type when the BINARY modifier is used. When the TEXT and BLOB types require more than 255 fields, MySQL provides two types: TEXT and BLOB. They all have different subtypes based on the size of the stored data. These large data types are used to store binary data types such as text blocks or images and audio files. The TEXT and BLOB types differ in classification and comparison. BLOB type is case sensitive, while TEXT is case insensitive. The size modifier is not used for various BLOB and TEXT subtypes. Values that are larger than the maximum range supported by the specified type are automatically truncated. When processing values of the date and time types, MySQL has five different data types available. They can be divided into simple date, time type, and mixed date, time type. According to the required precision, subtypes can be used in each shard type, and MySQL has a built-in function to convert diverse input formats into a standard format. Type size (byte) range format purpose DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD DATE value TIME 3 '-838: 59: 59'/'838: 59: 59 'hh: MM: SS time value or duration YEAR 1 1901/2155 yyyy year Value DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH: MM: SS mixed Date and Time Value TIMESTAMP 8 2037 00:00:00/January 1, when yyyymmdd hhmmss mixed Date and Time Value, TIMESTAMP