MySQL Use tips

1, MySQL has a lot of useful functions, good use of them can save a lot of time:

Group_concat () the value to be taken is concatenated with commas, so you can use:

select group_concat(distinct id) from tablename;

The results are similar to: 1,2,3,4,5

Unix_timestamp (), To_days (), now () These three functions are date-related functions, primarily for processing timestamp similar data in MySQL, and can be used to convert and compare with UNIX timestamps

selectnow()unix_timestamp(now())to_days(now());

Inet_aton (), Inet_ntoa () that's not much. IP network byte sequence conversion function

Select Inet_aton (' 127.0.0.1 '), Inet_ntoa (2130706433);

2, in the operation of the database is likely to be garbled characters, this situation is mainly due to the data table using the encoding and database default encoding inconsistent, you can use the following statement to change the encoding:

set names utf8

3, in the project development, sometimes encounter Client add emoji expression, how to store to MySQL database?
Method:
The UTF-8 encoding can be two, three, and four bytes. The emoji expression is 4 bytes, while MySQL UTF8 encodes up to 3 bytes, so data cannot be plugged in. The solution is: convert MySQL encoding from UTF8 to UTF8MB4
(1). Modify MY.CNF
[Mysqld]
Character-set-server=utf8mb4
[MySQL]
Default-character-set=utf8mb4
Restart MySQL after modification
(2). Log in to MySQL as root and modify the environment variables to
Character_set_client,character_set_connection,character_set_database,character_set_results,character_set_ Server is modified to UTF8MB4
(3). Convert the already built tables to UTF8MB4
ALTER TABLE table_name convert to Character set UTF8MB4 collate utf8mb4_bin; (Replace table_name with your table name)
4. Use the following statement to view data table related information:

show create table your_table;

MySQL Use tips