MySQL中常見的函數以及常見的問題匯總

本篇內容介紹了“MySQL中常見的函數以及常見的問題匯總”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

MySQL中常見的函數：

if語句：
	格式：IF(Condition,A,B)
	說明：當Condition為TRUE時，返回A；當Condition為FALSE時，返回B。

case when語句：
	舉例：
	SELECT t.message_id,t.title,
	CASE 
		WHEN TO_DAYS(t.date)= TO_DAYS(NOW()) THEN '今天' 
		WHEN TO_DAYS(t.date)= (TO_DAYS(NOW())-1) THEN '昨天' 
		ELSE date_format(t.date,'%Y-%m-%d') 
	END AS datestr
	FROM t_message t
	

字符串截取/連接函數：

	CONCAT函數：
		格式：
			CONCAT(columnName1,columnName2,'otherString')
		說明：返回一個字符串結果，該結果由參數中的值連接(不使用分隔符)而成，如果某一個參數為NULL ，則返回值為NULL。
	
	GROUP_CONCAT函數：
		格式：
			GROUP_CONCAT(columnName)
			GROUP_CONCAT(columnName SEPARATOR ';')
		說明：返回一個字符串結果，該結果由分組中的(columnName列的)值用分隔符(默認為英文逗號)拼接而成。

	RIGHT函數
		格式：right(str, length)
		說明：返回字符串str最右面的length個字符。

	INSTR函數
		格式：INSTR(str,substr) 
		說明：返回子串substr在字符串str中第一個出現的位置，位置從1開始計算。若在str中找不到substr則返回0。
		補充：
			LOCATE(substr,str,pos)：返回子串substr在字符串str中從pos開始第一個出現的位置。如果substr不是在str里面，返回0。

	SUBSTRING函數
		格式：substring(str, pos, length);
		說明：截取字符串。從pos開始，截取length長度。

		
字符串替換函數：
	舉例：將content字段值中的'{URL}'替換為download_url字段的值。
	select t.id, t.title, replace(content,'{URL}',t.download_url) from t_helpcenter t
		

時間函數：
	函數：
		NOW()									eg：2018-03-19 19:18:55
		CURDATE()								eg：2018-03-19
		DATE_SUB(CURDATE(), INTERVAL 7 day)		eg：若今天是2018-03-19，則結果為2018-03-12
		DATE_SUB(CURDATE(), INTERVAL 1 week)	eg：若今天是2018-03-19，則結果為2018-03-12
		DATE_SUB(CURDATE(), INTERVAL 1 month)	eg：若今天是2018-03-19，則結果為2018-02-19
		DATE_SUB(CURDATE(), INTERVAL 1 year)	eg：若今天是2018-03-19，則結果為2017-03-19
		DATE_FORMAT(CURDATE(), '%Y%m')			eg：201803
		quarter(NOW())							eg：若今天是2018-03-19(3月屬于第1季度)，則結果為1
		year(NOW())								eg：2018
		
	舉例：
		今天：	SELECT * FROM t_advertise_message WHERE TO_DAYS(update_time) = TO_DAYS(NOW())
		昨天：	SELECT * FROM t_advertise_message WHERE TO_DAYS(update_time) = TO_DAYS(NOW()) -1
		7天內：	SELECT * FROM t_advertise_message WHERE DATE(update_time) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
		本月：	SELECT * FROM t_advertise_message WHERE DATE_FORMAT(update_time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')
		本季度：SELECT * FROM t_advertise_message WHERE quarter(update_time) = quarter(NOW())
		本年度：SELECT * FROM t_advertise_message WHERE year(update_time) = year(NOW())
		
將字符串轉換為時間的函數：
	str_to_date('2000-05-24 14:00:00', '%Y-%m-%d %H:%i:%s')

MySQL中常見的問題：

1)MySQL關于查詢條件中：字符串類型的值忽略英文字母的大小寫以及字符串尾部包含空格的問題

	舉例：MySQL中執行下面3條查詢語句，查詢出來的結果完全相同。
		SELECT * FROM t_accounts WHERE account= "xiaoning"
		SELECT * FROM t_accounts WHERE account= "XiaoNing"
		SELECT * FROM t_accounts WHERE account= "xiaoning "
		
	分析：
		1)MySQL對CHAR或VARCHAR類型的值進行比較(=)時，會忽略字符串中英文字母的大小寫以及尾部的空格
		2)對CHAR和VARCHAR類型的值進行LIKE查詢時，會忽略英文字母的大小寫，但是不會忽略字符串尾部的空格

	解決方案：
		SELECT * FROM t_accounts WHERE account= BINARY "xiaoning"	或	SELECT * FROM t_accounts WHERE BINARY account= "xiaoning"
		SELECT * FROM t_accounts WHERE account= BINARY "XiaoNing"	或	SELECT * FROM t_accounts WHERE BINARY account= "XiaoNing"
		SELECT * FROM t_accounts WHERE account= BINARY "xiaoning "	或	SELECT * FROM t_accounts WHERE BINARY account= "xiaoning "
		
	說明：
		1>BINARY不是函數，是類型轉換運算符
		2>BINARY強制將后面的字符串轉換為一個二進制的字符串，可以理解為在字符串比較的時候區分英文字母的大小寫以及空格，即精確匹配。

		
2)對varchar類型的字段和數字進行比較時，mysql會把varchar轉換為數字：

	舉例：
		當varchar類型字段的首字符為非數字時(eg:asdf)，該字段會被轉換為數字0；
		當varchar類型字段的首字符為數字0時(eg:0abcd)，該字段會被轉換為數字0；
			eg：varchar類型字段=0 	只能排除首字符為非數字0的字符串
		同理：varchar類型字段=1 	只能排除首字符為非數字1的字符串
		
		
	
3)在MySQL客戶端中查看MySQL的安裝目錄：

	SELECT @@basedir AS MySqlDir FROM DUAL
	
4)MySQL中存儲超長字符串：
	MySQL中字段的類型設為：MEDIUMTEXT
	Mybatis中對應的類型為：JdbcType.LONGVARCHAR

其它：

replace into語句：

	原理：
		replace into語句首先會判斷表中是否已經存在該行數據(根據主鍵或唯一索引來判斷)，如果該行數據已存在，則刪除已存在的該行數據并插入新的數據；如果該行數據不存在，則直接將數據插入。
	注意：
		插入數據的表必須有主鍵或唯一索引，否則replace into語句會直接將數據插入到表中，從而可能導致表中出現重復的數據。
		如果要插入的數據已存在，那么，replace into語句的返回值為2 (受影響的行數為2)

	舉例：
		DDL：
			CREATE TABLE `t_site_id` (
			  `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;

		說明：
			1>t_site_id表有兩列：id、unique_column，表中只有一條數據：id=1，unique_column="a"
			2>執行：replace into t_site_id set unique_column="a" 后，表中的數據變成了：id=2，unique_column="a"
			3>在mybatis中執行replace into語句后，可以使用@SelectKey注解來獲取到自增后最新的id，從而我們可以獲取到一個全局唯一(且遞增)的id。
			代碼：
				@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);

“MySQL中常見的函數以及常見的問題匯總”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！