Mysql Data Type 2

Mysql Data Type 2: www.2cto.com MySQL Data type can be roughly divided into two categories, one is an integer, and 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 TINYINT1 byte (-128,127) (0,255) small integer SMALLINT2 byte (-32 768,32 767) (535) large integer MEDIUMINT3 byte (-8 388 608 388 607) (777 215 147) large integer INT or INTEGER4 byte www.2cto.com (-2 483 147 648,2 483 647) (294 967 295 233) BIGINT8 byte (-9 372 854 036 775 223 372 854 775 807 446 744 709 551 615) FLOAT4 byte (-3.402 823 466 E + 38, 1.175 494 351 E-38), 0, (1.175 494 351 E-38, 3.4 02 823 466 351 E + 38) 0, (1.175 494 351 E-38, 3.402 823 466 E + 38) single precision floating point value DOUBLE8 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 value 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 small value www.2cto.com INT type supported in MySQL five major integer types are TINYINT, SMALLINT, MEDIUM INT, 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. The string type www.2cto.com MySQL provides eight basic string types that can store a range from a simple character to a huge text block or binary string data. Type size usage CHAR0-255 byte fixed length string VARCHAR0-255 BYTE Variable Length string TINYBLOB0-255 byte no more than 255 characters binary string TINYTEXT0-255 byte short text string BLOB0-65 535 bytes binary form long text data TEXT0-65 535 bytes long text Data MEDIUMBLOB0-16 777 215 byte binary form moderate length text data MEDIUMTEXT0-16 777 215 byte medium length text data LOGNGBLOB0-4 294 967 295 byte binary form extremely large text data LONGTEXT0-4 294 967 295 byte extremely large text data the CHAR and VARCHAR types www.2cto.com CHAR 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 www.2cto.com size (byte) range format usage DATE31000-01-01/9999-12-31YYYY-MM-DD date value TIME3 '-838: 59: 59'/'2014: 59: 59' HH: MM: SS time value or duration YEAR11901/2155YYYY year value DATETIME81000-01-01 00:00:00/9999-12-31 23: 59: 59YYYY-MM-DD HH: MM: SS mixed DATE and TIME Value TIMESTAMP81970-01-01 00:00:00/2037 when yyyymmdd hhmmss mixed DATE and TIME Value, TIME Stamp DATE, TIME and TEAR type MySQL with DATE and TEAR type storage simple DATE value, use the TIME type to store the TIME value. These types can be described as strings or integer sequences without delimiters. If the description is a string, values of the DATE type should be separated by the font size, while values of the TIME type should be separated by colons. It should be noted that the TIME type value without the colon separator will be understood by MySQL as the duration rather than the timestamp. MySQL also maximizes the translation of the values of two numbers in the year of the date, or the two numbers entered for the TEAR type in the SQL statement. Because all TEAR values must be stored in four numbers. MySQL tries to convert the year of two numbers to the value of four numbers. Convert the value in the range of 00-69 to 2000-2069. Convert the value in the range of 70-99 to 1970-1979. If the Automatically converted MySQL value does not meet our needs, enter four numbers to indicate the year. In addition to the date and time data types, MySQL also supports the hybrid types of DATEYIME and TIMESTAMP. They can store dates and times as a single value. These two types are usually used to automatically store timestamps containing the current date and time, it can also play a good role in applications that need to execute a large number of database transactions and audit trails that require the establishment of a debugging and review purpose. If we do not explicitly assign values to fields of the TIMESTAMP type, or are assigned a null value. MySQL will automatically fill it with the current date and time of the system. Composite MySQL also supports two composite data types, ENUM and SET, which extend the SQL specifications. Although these types are technically string types, they can be considered as different data types. An ENUM type can obtain only one value from a SET, while a SET type can obtain any number of values from a SET. The ENUM type is similar to a single option because only one value can be obtained in the set. It is easy to understand when processing mutually exclusive data, such as human gender. An ENUM type field can get a value from the set or use null value. Other input causes MySQL to insert an empty string into this field. In addition, if the case of the inserted value does not match the case of the set value, MySQL will automatically convert the case of the inserted value to a value consistent with the upper and lower case of the set. The ENUM type can be stored as numbers in the system and indexed from 1. An ENUM type can contain a maximum of 65536 elements, one of which is retained by MySQL and used to store error information. This error value is represented by index 0 or an empty string. Www.2cto.com MySQL considers that the value in the ENUM type set is a valid input, and any other input will fail. This means that the location of the error record can be easily found by searching for rows that contain null strings or corresponding numeric indexes of 0. The SET type is similar to the ENUM type but not the same. The SET type can obtain any number of values from a predefined SET. What is the same as ENUM type is that any attempt to insert a non-predefined value in the SET Type field will cause MySQL to insert an empty string. If you insert a record with both valid and invalid elements, MySQL retains valid elements to Remove invalid elements. A set type can contain up to 64 elements. The value in the SET element is stored as a separated "bit" sequence, which indicates the elements corresponding to it. "Bit" is a simple and effective way to create an ordered element set. Besides repeated elements, the SET type cannot contain two identical elements. To find an invalid record from the SET type field, you only need to find the row that contains a null string or a binary value of 0. Author: hunting-household-seat