Mysql Data Types Interview Questions 15 in a Row

What is the purpose of the UNSIGNED property of an integer type?

Integer types in MySQL can use the optional UNSIGNED attribute to represent unsigned integers that do not allow negative values. Using the UNSIGNED attribute doubles the upper limit for positive integers because it does not require storing negative values.

For example, the value range of TINYINT UNSIGNED type is 0 ~ 255, while the value range of normal TINYINT type is -128 ~ 127. The value range of INT UNSIGNED type is 0 ~ 4,294,967,295, while the value range of normal INT type is -2,147,483,648 ~ 2,147, 483,647. 483,647.

For ID columns that increment from 0, using the UNSIGNED attribute can be a good fit because negative values are not allowed and you can have a larger upper range, providing more ID values available.

Difference between char and varchar

CHAR

The CHAR type is used to store fixed-length strings: MySQL alwaysAllocate enough space according to the length of the defined string. When storing CHAR values, MySQL removes the end space in the string At the same time, the CHAR value will employ spaces for remaining space filling as needed to facilitate comparison and retrieval. However, precisely because of its fixed length, it will take up extra space and is a space-for-time strategy;
CHAR is suitable for storing very short or approximate length strings. For example.CHAR is great for storing MD5 values for passwords, fixed-length IDs, etc., as these are fixed-length values。
CHAR is also better than VARCHAR for data that changes frequently because the fixed-length CHAR type takes up disk storage space that is continuously allocated and is less prone to fragmentation.
For very short columns, CHAR is also more efficient in terms of storage space than VARCHAR. For example, using CHAR(1) to store values with only Y and N would require only one byte with a single-byte character set, but VARCHAR(1) would require two bytes because there is an additional byte for the length of the record.

VARCHAR：

The VARCHAR type is used to store variable length strings and is the most common string data type. ItMore space efficient than fixed length types, because it uses only the necessary space (changing the storage space according to the length of the actual string).
VARCHAR requires either 1 or 2 extra bytes to record the length of the string: if the maximum length of the column is less than or equal to 255 bytes, only 1 byte is used to represent it, otherwise 2 bytes are used. Assuming the latinl character set, a VARCHAR(10) column would require 11 bytes of storage. a VARCHAR(1000) column would require 1002 bytes, since 2 bytes are needed to store length information.
VARCHAR saves storage space, so it helps performance as well. However, since rows are variable in length, it is possible to make a row longer than it was at the time of UPDATE, which results in extra work being required. If a row grows in space and there is no more space to store it within the page, different storage engines handle this situation differently. For example, MylSAM will split the row into different pieces for storage, and InnoDB will need to split the page to make the row fit inside the page.
The way of operating memory: For varchar data type, although the storage space on the hard disk are based on the actual length of the string to store space, but in the memory is based on the length of the varchar type definition to allocate the occupied memory space, but not based on the actual length of the string to allocate. Obviously, this will have a greater performance impact on sorting and temporary tables.

What is the difference between VARCHAR(100) and VARCHAR(10)?

VARCHAR(100) and VARCHAR(10) are both variable-length types, indicating that they can store up to 100 characters and 10 characters. Therefore, VARCHAR (100) can fulfill the need of storing a larger range of characters and has better business scalability. When VARCHAR(10) stores more than 10 characters, you need to modify the table structure.

Although VARCHAR(100) and VARCHAR(10) can store a different range of characters, but both store the same string, the storage space occupied by the disk is in fact the same, which is also a lot of people easy to misunderstand the point.

However, VARCHAR(100) consumes more memory. This is because when the VARCHAR type operates in memory, it usually allocates a fixed-sized block of memory to hold the value, i.e., it uses the length defined in the character type. For example, when sorting, VARCHAR(100) is used at a length of 100, which consumes more memory.

What is the difference between DECIMAL and FLOAT/DOUBLE?

The difference between DECIMAL and FLOAT is:DECIMAL is a fixed-point number and FLOAT/DOUBLE is a floating-point number; DECIMAL can store exact decimal values and FLOAT/DOUBLE can only store approximate decimal values.

DECIMAL is used to store decimals with precision requirements, such as currency-related data, to avoid the loss of precision associated with floating-point numbers.

In Java, MySQL's DECIMAL type corresponds to the Java class。

Difference between int(10) and char(10)?

The 10 in int(10) indicates the length of the displayed data, while char(10) indicates the length of the stored data.

Why are TEXT and BLOB not recommended?

Database specifications usually do not recommend the use of the BLOB and TEXT types, which have some disadvantages and limitations, for example:

Cannot have a default value.
You can't use in-memory temporary tables when using temporary tables; you can only create temporary tables on disk (as mentioned in the High Performance MySQL book).
Retrieval is less efficient.
Indexes cannot be created directly, you need to specify the prefix length.
May consume significant network and IO bandwidth.
May cause DML operations on tables to slow down.
……

What is the difference between DATETIME and TIMESTAMP?

The DATETIME type has no time zone information, TIMESTAMP is related to the time zone.

TIMESTAMP uses only 4 bytes of storage space, but DATETIME uses 8 bytes of storage space. However, this also creates a problem in that Timestamp represents a smaller range of time.

DATETIME：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
Timestamp：1970-01-01 00:00:01 ~ 2037-12-31 23:59:59

How is the Boolean type represented?

There is no specific boolean type in MySQL, instead, boolean values are represented by the TINYINT(1) type, which can store either 0 or 1, which corresponds to false or true, respectively.

Why it is not recommended to use null as a default value

Mysql doesn't recommend using Null as a column default not because you can't use indexes, but because:

The presence of NULL in an index column causes the optimizer to make index selections more complex and more difficult to optimize. For example, when doing index statistics, thecount(1),max(),min() omits rows with NULL values.。
A NULL value is a meaningless value, but it takes up physical space, so it poses a storage space problem because when InnoDB stores records, if there is a field in the table that is allowed to be NULL, then the row format (opens new window) isAt least 1 byte of space is used to store the list of NULL values.. It is recommended that "" or the default value of 0 be used instead of NULL

It is not recommended to use null as the default value, and theIt is recommended that the default value must be setThe reasons are as follows:

Since none of them are nullable, there must be a default value, otherwise it will report an error if you don't insert this column;
The database shouldn't be used to check for problems, you can't rely on mysql to report errors to tell the business that there's a problem, it should be up to the business to say whether it should be inserted or not;
There is no norm for DBAs to allow the use of null because different people use it differently.

unlikeTime of entry into force of the contract、Award time and other such uncontrollable fields, it is possible not to set the default value, but it is also necessary not null

Why foreign keys are prohibited

Foreign keys can degrade database performance. In MySQL, foreign keys are automatically indexed, which can make operations such as queries on that table slow, especially in large data tables.
Foreign keys can also limit adjustments and changes to the table structure. In practice, table structure changes are often required, and these changes can be very difficult to implement if foreign key constraints are used between tables. Changing the structure of a table involves all the child tables that have it as a parent, which can result in locking up the entire database table for an extended period of time, and may even result in data loss.
In MySQL, foreign key constraints may also cause deadlock problems. When trying to perform insert, update, or delete operations on data in multiple tables, the presence of foreign key constraints may result in deadlocks and the need to wait for other transactions to release the lock.
The use of foreign keys in MySQL also makes development more difficult. Developers need to handle the relationship of data between tables, and such handling requires more time and effort, as well as a deeper understanding of the database. Also, foreign keys increase the complexity of the code, making SQL statements difficult to understand and debug.

It's also mentioned in the Alibaba Developer's Handbook that theportal

What are the benefits of using self-incrementing primary keys?

A self-incrementing primary key allows the primary key index to keep the primary key index as incrementally sequential as possible for insertion and avoids page splitting, so the index is more compact and, when queried, more efficient.

Where is the self-adding primary key saved?

Different engines have different preservation strategies for self-added value:

The MyISAM engine's self-added value is stored in a data file.
Prior to MySQL 8.0, the self-add value of the InnoDB engine existed in memory, and after a MySQL restart, the value in memory was lost. each time a table was opened for the first time after a restart, the maximum value of the self-add value, max(id), would be looked for, and then the maximum value would be added by 1 as the self-add value of the table; MySQL version 8.0 logged the changes to the self-add value in the redo log. MySQL version 8.0 will record the changes in self-add value in redo log, and then rely on redo log to recover the changes when restarting.

Must the self-incrementing primary key be continuous?

Not necessarily, there are several scenarios that can cause a self-incrementing primary key to be discontinuous.

1. Unique key conflicts cause the self-incrementing primary key to be discontinuous. When we insert data into an InnoDB table with a self-incrementing primary key, if we violate the unique constraints of the unique index defined in the table, it will cause the insertion to fail. At this point the key value of the table's self-incrementing primary key is rolled back by adding 1. The next time you insert data again, you can no longer use the key value that was rolled up due to the last failed insertion, you must use the new rolled up key value.

2、Transaction rollback leads to discontinuity of self-incrementing primary key. When we insert data into an InnoDB table with a self-incrementing primary key, if we explicitly open a transaction, and then roll back the transaction for some reason, then the table's self-added value will also be rolled over, and then the newly inserted data will not be able to use the rolled over self-added value, but will need to re-apply for a new self-added value.

3. Bulk insertion leads to discontinuous self-incrementation. mySQL has a strategy for requesting self-incremented ids in bulk:

During the execution of the statement, the first time a self-incrementing id is requested, 1 self-incrementing id is assigned
After 1 is used up, the second application will be assigned 2 self-incremented ids
After 2 are used up, the third application will be assigned 4 self-incrementing ids
And so on, each application is twice as much as the previous one (the last application may not be used in full)

If the next transaction inserts data again, it does so based on the self-added value of the previous transaction. In this case, the self-added value is not continuous.

4. A self-increment step that is not 1 will also cause the self-increment primary key to be discontinuous.

Why can't InnoDB's self-replenishment be recycled?

Mainly to improve the efficiency and parallelism of inserting data.

Assuming that there are two transactions executing in parallel, when applying for self-additions, in order to avoid the two transactions applying for the same self-added id, they must add locks and then apply them sequentially.

Assuming that transaction A requests id=2 and transaction B requests id=3, the self-added value of table t is 4, and execution continues thereafter.

Transaction B commits correctly, but transaction A has a unique key conflict.

If transaction A is allowed to back off the incremental id, that is, change the current incremental value of table t back to 2, then the situation arises where there are already rows in the table with id=3, and the current incremental id value is 2.

Next, the other transactions that continue to execute apply to id=2 and then to id=3. At this point, the insert statement reports a "Primary Key Conflict" error.

And to resolve this primary key conflict, there are two ways:

Each time you request an id, you determine if the id already exists in the table, and if it does, you skip the id. However, this method is very costly. However, this method is very costly, because it is a very fast operation to request an id, and now you have to go to the primary key index tree to determine if the id already exists.
The problem with this approach is that the locking granularity is too large and the concurrency of the system is greatly reduced.

As you can see, both methods can lead to performance problems.

As a result, InnoDB abandons the "allow self-added id fallback" design, and does not fallback on self-added ids even if the statement fails.

The difference between utf8, utf8mb3 and utf8mb4.

utf8mb3: Only BMP (Basic Multilingual Plane) characters up to three bytes long are supported (supplementary characters are not supported).

utf8mb4: mb4, or most bytes 4, which means that up to 4 bytes are used to represent the full UTF-8, has the following characteristics:

BMP and supplementary characters are supported.
Up to four bytes are required for each multibyte character.

utf8mb4 is a superset of utf8 and is fully compatible with it. It is a new character set added to MySQL after version 5.5.3, and is capable of storing more characters in four bytes, including characters from almost every language in the world.

Comparison of differences

point of difference	utf8mb3	utf8mb4
Maximum number of bytes used	3	4
Supported Character Types	BMP	BMP + other characters
character type	Common Unicode Characters	Common Unicode characters + some rare Chinese characters + emoji + new Unicode characters, etc.
Unicode range	U0000 - U+FFFF (i.e. BMP)	U0000 - U+10FFFF
Storage space occupied	Slightly smaller (e.g., CHAR(10) requires 10 * 3 = 30 bytes of space; VARCHAR types use an additional 1 byte to record the length of the string)	Slightly larger (e.g., CHAR(10) requires 10 * 4 = 40 bytes of space; VARCHAR types use an additional 2 bytes to record the length of the string)
compatibility	Switching to utf8mb4 is generally not a problem, but be aware of whether there is enough storage space and whether the sorting rules change	Switching to utf8mb3 may be problematic, with characters missing, reporting errors or garbled code
safety	Slightly lower, more vulnerable to malicious string attacks	Higher, retain malicious strings, then report errors or garbled hints

How to choose? In a word, choose the most appropriate character set according to the specific business needs and actual situation.

Interview questions column

Java interview questions columnIt's online, so feel free to visit.

If you don't know how to write a resume, resume projects don't know how to package them;
If there's something on your resume that you're not sure if you should put on it or not;
If there are some comprehensive questions you don't know how to answer;

Then feel free to private message me and I will help you in any way I can.