best choice for MySQL data types It's important to choose the type of data carefully. Can improve the performance, the principle is as follows: Storage (memory, disk), saving I/O (retrieving the same data)calculate and then offload CPU load data types can be divided into three categories: numbers, characters, and periods ㈠ Digital ① Classification ② Specifying a width for an integer type doesn't make sense, say it, and probably also to show the number of characters, the human pointbecause, for storage and Computation, int (1) and int (100) are the same ③ for floating point, it is recommended to specify only the data type, not the precision ④ for fixed points, as far as possible only in the accurate calculation of decimals only used, such as the storage of financial databut some financial software in the Kingdom uses float as the wage type in its databaseThe ugly manifestation of naked capitalists, despise!!! ⑤ When the data volume is large, it is recommended to convert the real type to integer typeThe reason is simple:floating point imprecisefixed-point computing is costlyFor example:to keep the financial data accurate to one out of 10,000, you can multiply all the amounts by 1 million, then exist under bigint ㈡ String ① Classification How ② data is stored depends on the storage enginechar and varchar may not be the same in different engines in memory and diskHowever, for Char, the same on fill and intercept, because,This part is done at the server level. ③ Common char usage scenarios a short string or all values close to the same length, such as the MD5 value that stores the passwordfrequently changed columns, because char produces less fragmentsvery short columns, such as gender, char (1) are more efficient, after all, varchar (1) wasted 11 bytes ④ and the character set of that little trick CSDN has a very hot discussion post: Does anyone think that Unicode is equivalent to Utf-8? Click to see the discussion postI bright, this is actually not much to discuss, because, these two are fundamentally differentUnicode is a character encodingUTF8 is a character set recommended use of varchar for Unicode coded character set and wall splittingObviously, UTF8 is also Unicode encoded, and, with the international trend, UTF8 is the first choiceso, as a result, in the char and varchar competition, varchar is the biggest winner. You can view the character sets supported by MySQL Unicode and the current character set in the following ways[Plain]MySQL> select character_set_name from Character_setswhere DESCRIPTION like '%unicode% ' \g; *************************** 1. Row ***************************Character_set_name:utf8*************************** 2. Row ***************************CHARACTER_SET_NAME:UCS2*************************** 3. Row ***************************CHARACTER_SET_NAME:UTF8MB4*************************** 4. Row ***************************Character_set_name:utf16*************************** 5. Row ***************************Character_set_name:utf325 rows in Set (0.06 sec) mysql> status; -------------- MySQL Ver 14.14 distrib 5.5.16, for Linux (i686) using Editline wrapper Connection id:1Current Database:information_schemaCurrent User: [email protected]Ssl:not in useCurrent pager:stdoutUsing outfile: 'Using delimiter:; Server version:5.5.16-log Source distributionProtocol version:10Connection:localhost via UNIX socketServer characterset:latin1Db Characterset:utf8Client Characterset:utf8Conn. Characterset:utf8UNIX sockets:/tmp/mysql.sockUptime:2 min 9 sec ⑤ The use of varchar is recommended for InnoDBNow many occasions have used the InnoDB, counted down, varchar wins again ⑥ storing Big Data BLOB: Binary storage, no character set, or collationTEXT: Stored by character, with character set and collation It is best to avoid using these two, if possible, to separate the Blob text column into a separate table for large deletions or updates that use BLOBs or text, theIt is recommended to use optimize table defragmentation regularly ㈢ Date and Time type datatime, timestamp, and date are common.from storage, timestamp accounts for 4 bytes, two other 8 bytesSo, if there is no accident, it is best to use timestamp, even if it is more naughty, such as time zone related
Best choice for MySQL data types
