Tag: SQL

[MySQL] 讓mysql識別table 不分大小寫(case insensitive)

以下兩種SQL,MySQL是否會將他們識別程不同的資料表呢?

select * from users
select * from Users

答案是:可以,也不可以

一般來說資料表的名稱都會使用小寫來命名,雖然我也有遇過一些是用大寫命名,但第一次遇到有混用的,才發現我需要在開發環境的my.cnf加上一個設定,才能讓以上兩個sql查到同一張資料表,而且我有發現只有我在建立開發環境的時候碰到這個問題,因為團隊的其他人是用Mac,在預設的情況下在Mac和Windows安裝MySQL,它會將SQL的table name用(case insensitive)的方式來看,而Linux預設則會用(case sensitive)的方式,這和OS的File system有關,但有沒有方式可以讓MySQL在Linux上跑的時候也case insensitive呢?

只要在my.cnf將lower_case_table_names設1

[mysqld]
lower_case_table_names = 1

不管你的table是大寫或小寫mysql都會將他們視為小寫

參考來源:

https://dev.mysql.com/doc/refman/5.6/en/identifier-case-sensitivity.html

Advertisements

[SQL]having的使用時機

一般來說having常常搭配group by一起使用

假設有一張articles的資料表,共有10筆資料如下:

現在有個情境是「取得文章分類的流覽次數超過5且依照次數由高到低排序」

若是一個初學SQL的新手又不認識having的用法,可能會寫成這樣:

SELECT
	category_id,
	SUM(view_cnt) AS view_cnt
FROM
	`articles`
WHERE
	view_cnt > 5;
GROUP BY
	category_id
ORDER BY
	view_cnt DESC

乍看之下好像是對的,但其實這個SQL變成「取得文章流覽次數超過5的文章分類流覽次數並依照次數由高到低排序」,這是因為WHERE會套用在GROUP BY之前,所以在GROUP BY前會把文章瀏覽次數小於5的排除掉才進行GROUP BY,這個結果不是我們要的!

正確的SQL是利用GROUP BY搭配HAVING

SELECT
	category_id, SUM(view_cnt)  AS total_view_cnt
FROM
	`articles`
GROUP BY category_id
HAVING total_view_cnt > 5
ORDER BY total_view_cnt DESC

如果不想使用HAVING的話也可以利用子查詢的方式,但比較不建議

SELECT * FROM (
SELECT
	category_id, SUM(view_cnt)  AS total_view_cnt
FROM
	`articles`
GROUP BY category_id
ORDER BY total_view_cnt DESC
) A
WHERE A.total_view_cnt > 5;

因為查兩次,多此一舉

[Codeingter] 回傳insert、update、delete的執行結果

話說我自己在做MVC開發的時後,有個習慣就是一定要回傳SQL的執行結果

因為這關係到在controller的寫法

但,前幾天在開發的時候遇到一個問題,明明table的資料沒有更新到,但卻回傳true

花了一點時間看了ci的source code和Google

證明是我的理解錯誤…

例如:這邊return的用意是讓我可以在controller判斷在Model的執行結果


public function updateData($id, $data){

  $this->db->where('id', $id);
  return $this->db->update('mytable', $data);

}

但是!!這裡需要理解的地方是$this->db->update() return的結果是「執行update sql是否成功」,不代表資料是否有被更新

所以,比較好的寫法是:


public function updateData($id, $data){

  $this->db->where('id', $id);
           ->update('mytable', $data);

  return ($this->db->affected_rows() > 0) ? TRUE : FALSE; 

}

更簡潔的寫法


public function updateData($id, $data){

  $this->db->where('id', $id);
           ->update('mytable', $data);

  return $this->db->affected_rows() > 0;

}

這樣做的好處是,在controller可以明確的知道table的資料到底有沒有被異動