Some tips on MySQL (continuous update) bitsCN. com1. use RAND () to extract random rows in MySQL. the method for generating random numbers is the RAND () function. you can use this function together with the order by clause to perform random row extraction. The principle is that order by rand () can sort data randomly. 2. use the with rollup clause of group by for statistics in SQL statements. use the WITH ROLLUP clause of GROUP BY to retrieve more GROUP aggregation information. It not only retrieves the aggregation information of each GROUP like the general group by statement, but also the overall aggregation information of the GROUP class. When ROLLUP is used, the order by clause cannot be used for result sorting at the same time because they are mutually exclusive. With rollup reflects an OLAP idea. that is to say, the group by statement can satisfy the aggregate information values of any GROUP and GROUP combination you want after execution. 3. some functions in MySQL, CONCAT (str1, str2,...), return strings produced by connection parameters. If any parameter is NULL, the return value is NULL.
mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL'mysql> SELECT CONCAT('My', NULL, 'QL'); -> NULLmysql> SELECT CONCAT(14.3); -> '14.3'
The group_concat function concatenates the obtained values with commas.
select group_concat(id) from table_name;
The result is (1, 2, 3, 4, 5)
CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result...] [ELSE result] end case when [condition] THEN result [WHEN [condition] THEN result...] [ELSE result] in the return result of the first scheme, value = compare-value. The returned results of the second solution are the real results of the first case. If no matching result value exists, the result after ELSE is returned. If no ELSE part exists, the return value is NULL.
IF (expr1, expr2, expr3) IF expr1 is True, expr2 is returned; otherwise, expr3 is returned. Expr1 is calculated as an integer. that is to say, if you are verifying a floating point or string value, you should use a comparison operation for testing.
mysql> SELECT IF(1>2,2,3); -> 3mysql> SELECT IF(1<2,'yes ','no'); -> 'yes'mysql> SELECT IF(STRCMP('test','test1'),'no','yes'); -> 'no'
IFNULL (expr1, expr2) if expr1 is not NULL, the returned value of IFNULL () is expr1; otherwise, the returned value is expr2. The returned value of IFNULL () is a number or string, depending on the context in which it is used.
mysql> SELECT IFNULL(1,0); -> 1mysql> SELECT IFNULL(NULL,10); -> 10mysql> SELECT IFNULL(1/0,10); -> 10mysql> SELECT IFNULL(1/0,'yes'); -> 'yes'
NULLIF (expr1, expr2) if expr1 = expr2 is true, the return value is NULL; otherwise, the return value is expr1. This is the same as case when expr1 = expr2 then null else expr1 END.
mysql> SELECT NULLIF(1,1); -> NULLmysql> SELECT NULLIF(1,2); -> 1
Coalesce function, returns the first non-null value in the parameter.
select coalesce(a,b,c) from table_name;
If a is not null, select a. If a is null, select B. If B is null, select c. If a, B, and c are both null, returns null.
GREATEST (value1, value2,...) when there are two or more parameters, the return value is the maximum (maximum) parameter. The comparison parameters are based on the same rule as LEAST.
mysql> SELECT GREATEST(2,0); -> 2mysql> SELECT GREATEST(34.0,3.0,5.0,767.0); -> 767.0mysql> SELECT GREATEST('B','A','C'); -> 'C'
UNIX_TIMESTAMP (), UNIX_TIMESTAMP (date) If no parameter is called, a Unix timestamp ('2017-01-01 00:00:00 'seconds after GMT) is returned as an unsigned integer. If you use date to call UNIX_TIMESTAMP (), it will return the parameter value in the form of the number of seconds after '2017-01-01 00:00:00 'GMT. Date can be a DATE string, a DATETIME string, a TIMESTAMP, or a number in the YYMMDD or YYYMMDD format of the local time.
mysql> SELECT UNIX_TIMESTAMP(); -> 882226357mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00'); -> 875996580
TO_DAYS (date) specifies a date, and returns a number of days (days starting from 0 in the year ).
mysql> SELECT TO_DAYS(950501); -> 728779mysql> SELECT TO_DAYS('1997-10-07'); -> 729669
MD5 (str), returns the MD5 value of str. It is often used to encrypt data in applications. Select MD5 ('20140901 ')
INET_ATON (IP address), returns the network byte order of the IP address to indicate INET_NTOA (num), and returns the IP address of the network byte code. 4. when you need to add a unique index to some fields in the table to delete duplicate data in the table, and the fields need to be cleaned up repeatedly, many DBAs should have encountered this problem. In general, I always want to keep only one record in the database and delete others. However, such an SQL statement is always inefficient to write. what should I do? In fact, you can change your mind by selecting one of the duplicates, saving them to a temporary table, deleting all the duplicates in the original table, and then inserting all the databases in the temporary table into the original database, this is a common and efficient approach. 5. import a large amount of data into the MySQL database to accelerate the import of a large amount of data to tables of the MyISAM storage engine.
ALTER TABLE tbl_name DISABLE KEYS;loading the dataALTER TABLE tbl_name ENABLE KEYS;
Disable keys and enable keys are used to ENABLE or DISABLE the update of non-unique indexes in the MyISAM table.
Because of the primary key clustered index relationship, InnoDB is slower and slower to import without a primary key or non-sequence primary key. how can we quickly migrate data to InnoDB? The power of MyISAM is very reliable. First, disable the Buffer Pool of InnoDB, leave the memory empty, and create a MyISAM table without any indexes. Then, just insert it, concurrent_insert = 2, and insert it concurrently at the end of the file. After the insertion is complete, alter table adds the index. remember that there is ENGINE = InnoDB, and MyISAM is transferred to InnoDB. this is far faster than inserting unordered data into InnoDB directly. BitsCN.com
