About the selection of time date type and string type in MySQL

1. Usage of DATETIME and TIMESTAMP

There are many data types in MySQL that represent time and date, mainly YEAR, TIME, DATE, DATETIME, TIMESTAMP, etc.

1. Similarities

Both datetime and timestamp can represent data in the format of YYYY-MM-DDHH:MM:SS year-month-day-hour-minute-second.

2. Differences

Datetime storage has nothing to do with time zone (specifically, datetime only supports one time zone, which is the time zone of the current server when stored), while timestamp storage is related to time zone.

The precision of datetime and timestamp is seconds. Datetime is independent of the time zone and has a wide storage range (1001-9999). Timestamp is related to the time zone and has a small storage range (1970-2038).

3. Selection

There is not much difference between TIMESTAMP and DATETIME except for the storage range and storage method. Of course, TIMESTAMP is more appropriate for cross-time zone business.

2. Usage of varchar and text data types

When storing strings in MySQL, you can use char, varchar, or text types.

1. Similarities

Both varchar and text can store variable-length strings and the upper limit of the string length is 65535 bytes.

2. Differences

varchar is fast, does not waste space, does not process trailing spaces, has an upper limit of 65535 bytes, but has a storage length of 65532 bytes. If the length is less than 255 bytes, 1 byte is used to store the length. If the length is greater than 255 bytes, 2 bytes are used to store the length. text, stores variable length data, has a slow speed, does not waste space, does not process trailing spaces, has an upper limit of 65535 bytes, and uses extra space to store the data length, so all 65535 bytes can be used.

You cannot place an index on a TEXT column (except a full-text index). For text, you can only add a prefix index, and the maximum size of a prefix index is 1000 bytes.

text has no default value

When varchar is larger than a certain value, it will be automatically converted to text. The general rules are as follows:

Larger than varchar(255) becomes tinytext

Larger than varchar(500) becomes text

Larger than varchar(20000) becomes mediumtext

3. Selection

1. Use varchar for fields that change frequently;

2. If you know the fixed length, use char;

3. For data exceeding 255 bytes, only varchar or text can be used;

4. Don't use text where varchar can be used;

5. If you can use numeric fields, try to use numeric types instead of string types. This will reduce query and connection performance and increase storage overhead. This is because the engine compares each character in the string one by one when processing the query and connection, and for numeric types, only one comparison is enough;

6. The impact of storage engine on the selection of CHAR and VARCHAR:

For the MyISAM storage engine, it is best to use fixed-length data columns instead of variable-length data columns. This makes the entire table static, making data retrieval faster and trading space for time. For the InnoDB storage engine, it is best to use variable-length data columns, because the storage format of InnoDB data tables does not distinguish between fixed-length and variable-length. Therefore, using CHAR is not necessarily better than using VARCHAR. However, since VARCHAR is stored according to the actual length, it saves space and is better for disk I/O and total data storage.

This is the end of this article about the selection of time and date types and string types in MySQL. For more relevant content on the selection of time and date types and string types in MySQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!