MySQL中常見的函數(shù)以及常見的問題匯總

本篇內(nèi)容介紹了“MySQL中常見的函數(shù)以及常見的問題匯總”的有關(guān)知識，在實(shí)際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

MySQL中常見的函數(shù)：

if語句：
	格式：IF(Condition,A,B)
	說明：當(dāng)Condition為TRUE時，返回A；當(dāng)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
	

字符串截取/連接函數(shù)：

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

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

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

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

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

時間函數(shù)：
	函數(shù)：
		NOW()									eg：2018-03-19 19:18:55
		CURDATE()								eg：2018-03-19
		DATE_SUB(CURDATE(), INTERVAL 7 day)		eg：若今天是2018-03-19，則結(jié)果為2018-03-12
		DATE_SUB(CURDATE(), INTERVAL 1 week)	eg：若今天是2018-03-19，則結(jié)果為2018-03-12
		DATE_SUB(CURDATE(), INTERVAL 1 month)	eg：若今天是2018-03-19，則結(jié)果為2018-02-19
		DATE_SUB(CURDATE(), INTERVAL 1 year)	eg：若今天是2018-03-19，則結(jié)果為2017-03-19
		DATE_FORMAT(CURDATE(), '%Y%m')			eg：201803
		quarter(NOW())							eg：若今天是2018-03-19(3月屬于第1季度)，則結(jié)果為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天內(nèi)：	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())
		
將字符串轉(zhuǎn)換為時間的函數(shù)：
	str_to_date('2000-05-24 14:00:00', '%Y-%m-%d %H:%i:%s')

MySQL中常見的問題：

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

	舉例：MySQL中執(zhí)行下面3條查詢語句，查詢出來的結(jié)果完全相同。
		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類型的值進(jìn)行比較(=)時，會忽略字符串中英文字母的大小寫以及尾部的空格
		2)對CHAR和VARCHAR類型的值進(jìn)行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不是函數(shù)，是類型轉(zhuǎn)換運(yùn)算符
		2>BINARY強(qiáng)制將后面的字符串轉(zhuǎn)換為一個二進(jìn)制的字符串，可以理解為在字符串比較的時候區(qū)分英文字母的大小寫以及空格，即精確匹配。

		
2)對varchar類型的字段和數(shù)字進(jìn)行比較時，mysql會把varchar轉(zhuǎn)換為數(shù)字：

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

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

其它：

replace into語句：

	原理：
		replace into語句首先會判斷表中是否已經(jīng)存在該行數(shù)據(jù)(根據(jù)主鍵或唯一索引來判斷)，如果該行數(shù)據(jù)已存在，則刪除已存在的該行數(shù)據(jù)并插入新的數(shù)據(jù)；如果該行數(shù)據(jù)不存在，則直接將數(shù)據(jù)插入。
	注意：
		插入數(shù)據(jù)的表必須有主鍵或唯一索引，否則replace into語句會直接將數(shù)據(jù)插入到表中，從而可能導(dǎo)致表中出現(xiàn)重復(fù)的數(shù)據(jù)。
		如果要插入的數(shù)據(jù)已存在，那么，replace into語句的返回值為2 (受影響的行數(shù)為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，表中只有一條數(shù)據(jù)：id=1，unique_column="a"
			2>執(zhí)行：replace into t_site_id set unique_column="a" 后，表中的數(shù)據(jù)變成了：id=2，unique_column="a"
			3>在mybatis中執(zhí)行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中常見的函數(shù)以及常見的問題匯總”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！