Some tricky operations of MySQL

Create table statement

1. The table structure is completely copied
create table user_bak LIKE user;

2. Use some fields to build a table
create table user_bak select now() as time ;

3. Force conversion of field values ​​when creating a table
create table user_bak select CAST('2019-8-01' as UNSIGNED) as time;

4. Temporary table
Explanation: It is only valid for the current session. If there is a table with the same name, the original table is hidden and invisible, and it is automatically cleared when the session ends.
create temporary table user_bak like user;
drop temporary table user_bak;
Cast type coercion
grammar:
Cast(field name as converted type)

Supported Types:
CHAR[(N)] character type
DATE date type
DATETIME date and time type
DECIMAL float
SIGNED int
TIME time type

Scenes:
1. Solve the problem of importing emoticons when querying utf8 characters, resulting in an error;
Create a new partition table
Example
create table user_bak (
id int(11) UNSIGNED AUTO_INCREMENT ,
`name` varchar(200) DEFAULT null COMMENT 'name',
rand_num int(11) DEFAULT NULL COMMENT 'random number',
birthday datetime default null comment 'birthday',
PRIMARY KEY (`id`,rand_num)
) ENGINE = INNODB partition by RANGE (rand_num)
(
PARTITION p0 VALUES less THAN (20),
PARTITION p1 VALUES less THAN (40),
PARTITION p2 VALUES less THAN (60),
PARTITION p3 VALUES less THAN (80),
PARTITION p4 VALUES less THAN MAXVALUE
);

be careful
1.PRIMARY must contain the fields of the partition
2. Partitions cannot be created separately, they must be created when creating a table
Common exception
1. ERROR 1064 Cannot create partition alone
Solution: Create a partition when building a table

2.ERROR 1503 The primary key must contain all columns in the partition function

Solution: The field that creates the partition must be placed in the primary key index
subquery

-- 1.ALL - The query returns a single result, similar to the in operation
select * from user_bak where (id)
>= ALL(select id from user_bak where id = 10)

-- 2.ANY & SUM - the same effect, similar to or operation
select * from user_bak where (name,id)
= SOME(select name,id from user_bak where id = 1 or name = 'eee')

FullText full text search

Full text search type
1. Natural Language Search - Search for information containing matching words
2. Boolean pattern search -
3. Query expansion search

Conditions that need to be met to create an index
1. The table type is MyISAM, and InnoDB support was introduced after version 5.6
2. The field type can only be char/varchar/text type
3. Full-text search will automatically ignore common words (the probability of occurrence in records is more than 50%) - verification can be found
4. Stop words will be filtered out (the/after/other, etc.)
5. Less than 4 characters will be ignored and cannot be found (default 4-84 characters range, can be changed)
grammar

-- natural language
select *,match(`name`) against('good boy')
as 'percentage' from `user` where match(`name`) against('good boy');

-- boolean mode
select *,match(`name`) against('good boy' in boolean MODE) as 'percentage' from `user` where match(`name`) against('good boy' in boolean MODE);

-- Content order matches exactly
select *,match(`name`) against('"good boy"' in boolean MODE) as 'percentage' from `user` where match(`name`) against('"good boy"' in boolean MODE);

-- extended query
select *,match(`name`) against('good boy' with query expansion) as 'percentage' from `user` where match(`name`) against('good boy' with query expansion);

Modify the query character length
1. ft_min_word_len in my.cnf file

2. Rebuild FullText index or quick fix
repair table table_name quick;
character set
There are character set related system settings
character_set_system character set for storage
character_set_server server default character set
collation_server system collation
character_set_database database character set
collation_database database collation
character_set_client The character set used by the client to send SQL to the server
character_set_result indicates the character set used by the server when returning the result
character_set_connection String to use when connecting
character_set_filesystem file system character set

Spatial value

OpenGIS Specification

point type value, only supports InnoDB/MyISAM/NDB/ARCHIVE engine
point(xxxx,xxxx)
Fuzzy match query
1.like
% matches any number of character sequences
_ can only match a single character
2.REGEXP-regular query
Authorize after creating a new user
-- % means all IPs can be connected
CREATE USER `username`@`%` IDENTIFIED BY 'password';
grant all privileges on jwgateway.* to 'username'@'%' identified by 'password';
select * from mysql.user;
Determine if time overlaps with existing records
-- 1. Method 1
SELECT * FROM test_table
WHERE (start_time >= startT AND start_time < endT)
OR (start_time <= startT AND end_time > endT)
OR (end_time >= startT AND end_time < endT)

-- 2. Method 2
SELECT * FROM test_table WHERE NOT ( (end_time < startT OR (start_time > endT) )
Judgment time intervals cannot overlap
set @start='2022-06-08',@end='2022-06-10';
select * FROM xxx WHERE
(
(start_time <= @start and end_time >= @end )
or (start_time >= @start and end_time >= @end and start_time < @end)
or (start_time <= @start and end_time <= @end and end_time > @start )
or (start_time >= @start and end_time <= @end)
)

-- explain
/*
time overlap
startTime endTime
start | end |
start | | end
| start end |
| |
*/
MySQL8.0 big data table adds varchar field
MySQL8.0 official website documentation
-- Natively only supports appending fields in the table, and cannot insert fields at random positions
update xxx add column name varchar(255),ALGORITHM=INSTANT;

/*
Role: Specifies the type of algorithm used by the operation
COPY: Performs an operation on a copy of the original table and copies table data from the original table to the new table row by row. Concurrent DML is not allowed.
The INPLACE: operation avoids copying table data, but rebuilds the table in place. An exclusive metadata lock on a table can be acquired briefly during the prepare and execute phases of an operation. In general, concurrent DML is supported.
INSTANT: The operation can only modify metadata in the data dictionary. During preparation and execution, no exclusive metadata locks are acquired on the table, and table data is not affected, allowing the operation to proceed immediately. Concurrent DML is allowed. (Introduced in MySQL 8.0.12)

INSTANT principle:
in INNODB_COLUMNS.DEFAULT_VALUE, INNODB_COLUMNS.HAS_DEFAULT, INNODB_TABLES.INSTANT_COLS
Add configuration information to the table, identify the number of fields before adding the instant field, whether the instant field has a default value, and the field name added by the instant;
The added fields will not be written to the db file. Only after the data is manipulated (insert/update), the data of the complete structure will be updated to the db file.
*/
View the connection password in Navicat Premium
1. NaivatPremium export connection
Note: Be sure to check the export password when exporting! ! !
The Password value in the export file is the encrypted password
decrypt
1. Open the URL https://tool.lu/coderunner/ and select php in the upper left corner
2. Copy the following code into the code box and modify the encrypted string in the third-to-last line of code
3. Click Run
4. If the execution fails, modify the version number 11/12
ps: Thanks for the code provided by the big guy, I can't find the source anymore