Shulou(Shulou.com)06/01 Report--

This article introduces the knowledge of "summary of common functions and common problems in MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Common functions in MySQL:

If statement: format: IF (Condition,A,B) description: return A when Condition is TRUE; return B when Condition is FALSE. Case when statement: for example: SELECT t.messageroomidjournal. Title, CASE WHEN TO_DAYS (t.date) = TO_DAYS (NOW ()) THEN 'today' WHEN TO_DAYS (t.date) = (TO_DAYS (NOW ())-1) THEN 'yesterday' ELSE date_format (t.date) '% Ymuri% mmi% d') END AS datestr FROM t_message t string intercept / concatenation function: CONCAT function: format: CONCAT (columnName1,columnName2,'otherString') description: returns a string result The result is concatenated by the values in the parameter (without using delimiters), and if one of the parameters is NULL, the return value is NULL. GROUP_CONCAT function: format: GROUP_CONCAT (columnName) GROUP_CONCAT (columnName SEPARATOR';') description: returns a string result, which is made up of values in the group (columnName column) concatenated with a delimiter (default is English comma). RIGHT function format: right (str, length) description: returns the rightmost length characters of the string str. INSTR function format: INSTR (str,substr) description: returns the position of the first occurrence of the substring substr in the string str, starting with 1. Returns 0 if substr is not found in str. Add: LOCATE (substr,str,pos): returns the position where the substring substr appears first from pos in the string str. If substr is not in str, return 0. SUBSTRING function format: substring (str, pos, length); description: intercept strings. Starting with pos, intercept the length length. String replacement function: for example, replace'{URL}'in the value of the content field with the value of the download_url field. Select t.id, t.title, replace (content,' {URL}' T.download_url) from t_helpcenter t time function: function: NOW () eg:2018-03-19 19:18:55 CURDATE () Eg:2018-03-19 DATE_SUB (CURDATE () INTERVAL 7 day) eg: if today is 2018-03-19, The result is 2018-03-12 DATE_SUB (CURDATE (), INTERVAL 1 week) eg: if today is 2018-03-19, the result is 2018-03-12 DATE_SUB (CURDATE (), INTERVAL 1 month) eg: if today is 2018-03-19, the result is 2018-02-19 DATE_SUB (CURDATE (), INTERVAL 1 year) eg: if today is 2018-03-19 The result is 2017-03-19 DATE_FORMAT (CURDATE (),'% Y% m') eg:201803 quarter (NOW ()) eg: if today is 2018-03-19 (March belongs to the first quarter) Then the result is 1 year (NOW ()) eg:2018 for example: today: SELECT * FROM t_advertise_message WHERE TO_DAYS (update_time) = TO_DAYS (NOW ()) yesterday: SELECT * FROM t_advertise_message WHERE TO_DAYS (update_time) = TO_DAYS (NOW ())-within 17 days: SELECT * FROM t_advertise_message WHERE DATE (update_time) > = DATE_SUB (CURDATE ()) INTERVAL 7 DAY) this month: SELECT * FROM t_advertise_message WHERE DATE_FORMAT (update_time,'%Y%m') = DATE_FORMAT (CURDATE () '% Y% m') this quarter: SELECT * FROM t_advertise_message WHERE quarter (update_time) = quarter (NOW ()) this year: SELECT * FROM t_advertise_message WHERE year (update_time) = year (NOW ()) A function that converts a string into time: str_to_date ('2000-05-24 1414GRV 0000' '% Y-%m-%d% HRV% iRU% s')

Common problems in MySQL:

1) MySQL on the query condition: the value of string type ignores the case of English letters and contains spaces at the end of the string. For example: the following three query statements are executed in MySQL, and the query results are exactly the same. SELECT * FROM t_accounts WHERE account= "xiaoning" SELECT * FROM t_accounts WHERE account= "XiaoNing" SELECT * FROM t_accounts WHERE account= "xiaoning" Analysis: 1) when MySQL compares values of type CHAR or VARCHAR (=) The case of the Chinese and English letters and the trailing space of the string will be ignored. 2) when LIKE queries for values of type CHAR and VARCHAR, the case of the English letters will be ignored. But the space solution at the end of the string will not be ignored: SELECT * FROM t_accounts WHERE account= BINARY "xiaoning" or SELECT * FROM t_accounts WHERE BINARY account= "xiaoning" SELECT * FROM t_accounts WHERE account= BINARY "XiaoNing" or SELECT * FROM t_accounts WHERE BINARY account= "XiaoNing" SELECT * FROM t_accounts WHERE account= BINARY "xiaoning" or SELECT * FROM t_accounts WHERE BINARY account= "xiaoning" description: 1 > BINARY is not a function It is the type conversion operator 2 > BINARY that forces the following string to be converted to a binary string, which can be understood as distinguishing the case and spaces of English letters when comparing strings, that is, exact matching. 2) when comparing a field of type varchar with a number, mysql converts varchar to a number: for example, when the first character of a field of type varchar is non-numeric (eg:asdf), the field is converted to the number 0; when the first character of a field of type varchar is the number 0 (eg:0abcd), the field is converted to the number 0. Eg:varchar type field = 0 can only exclude strings whose first character is non-numeric 0. Similarly, varchar type field = 1 can only exclude strings whose first character is non-numeric 1. 3) View MySQL installation directory in MySQL client: SELECT @ @ basedir AS MySqlDir FROM DUAL 4) extra-long strings are stored in MySQL: the field type in MySQL is set to: the corresponding type in MEDIUMTEXT Mybatis is: JdbcType.LONGVARCHAR

Other:

Replace into statement: principle: the replace into statement first determines whether the row of data already exists in the table (based on the primary key or unique index). If the row of data already exists, delete the existing row of data and insert the new data; if the row of data does not exist, insert the data directly. Note: the table that inserts the data must have a primary key or unique index, otherwise the replace into statement will insert the data directly into the table, which may result in duplicate data in the table. If the data to be inserted already exists, then The return value of the replace into statement is 2 (the number of rows affected is 2) for example: DDL: CREATE TABLE `tuplositeid` (`id` bigint (20) NOT NULL AUTO_INCREMENT, `unique_ column` varchar (1) DEFAULT NULL, PRIMARY KEY (`id`) UNIQUE KEY `unique_ column` (`unique_ column`) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 Note: 1 > the t_site_id table has two columns: id and unique_column. There is only one piece of data in the table: id=1,unique_column= "a" 2 > after executing: replace into t_site_id set unique_column= "a", the data in the table becomes: id=2,unique_column= "a" 3 > after executing the replace into statement in mybatis You can use the @ SelectKey annotation to get the latest id since the increment, so we can get a globally unique (and incremented) id. Code: @ Insert ({"replace into t_site_id set unique_column='a'"}) @ SelectKey (before = false, keyProperty = "id", resultType = Long.class, statementType = StatementType.STATEMENT, statement = "SELECT LAST_INSERT_ID ()") public Long getNewSiteId (ReqAddSite req) This is the end of the summary of common functions and common questions in MySQL. Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.