【Mysql】json_extract

json_extract 可抓取在資料中json的值

範例：

// 抓取json第1個跟第0個值
SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]');
結果：20 | 10

// 抓取data欄位，json名稱為name的值
select json_extract(data,'$.name'),json_extract(data,'$.tel') from tab_json 

直接抓取json第幾個值

CREATE TABLE tj10 (a JSON, b INT);
// 建立資料
INSERT INTO tj10 VALUES ("[3,10,5,17,44]", 33), ("[3,10,5,17,[22,44,66]]", 0);
// 抓取
SELECT a->"$[4]" FROM tj10;
結果：44 | [22,44,66]

更多資訊要看mysql官網：連結

【mysql】預設編碼

常常在新增資料庫或資料庫欄位時，

預設編碼都變成 latin1_general_ci，

這時可以改 my.ini (windows) my.cnf (linux)，

開啟或加上以下設定：

[mysqld] character-set-server=utf8 collation-server=utf8_general_ci init-connect='SET NAMES utf8' [client] default-character-set=utf8

延伸：utf8mb4_general_ci V.S. utf8mb4_unicode_ci

【PHP、MySQL】SQL搜尋分段LOG

今天要抓出半年內每個商品被點擊次數，在存入 PROD_CLICK
因為 LOG 量太大，所以每個月都會產生新的表單來儲存
類似：PROD_LOG_1606 ~ PROD_LOG_1612
所以要搜尋半年就會有6個資料表
步驟：
1.依目前日期及指定幾個月算出有幾個資料表

方法很多，可以直接用月份去減，但為了比較活我這邊是用天數去減，
$prod_click_day = $SS->getData('prod_click_day'); // 抓出設定天數
$day = strtotime("-$prod_click_day day"); // 用時間戳記來算差別的日期
$difMonth = date("m",time() - $day); // 算出與面前相差的月份

【SQL】if else

基本

PHP

   if ($score >= 90)
     $str = 'A';
    elseif ($score >= 70 && $score < 90) 
     $str = 'B';
    elseif ($score >= 60 && $score < 70) 
     $str = 'C';  
    else{
     $str = 'D';  
    }
     
  

SQL

   CASE  
     WHEN score >= 90 THEN 'A'
      WHEN score >= 90 THEN 'B'
      WHEN score >= 90 THEN 'C'
      ELSE 'D'
    END AS str
  

欄位值

Name	Answer
張一	1
王二	2
李三	3

    SELECT Name, 
     case Answer
       when 1 then '喜歡'
       when 2 then '不喜歡'
       when 3 then '一般'
      END
    FROM table;
    
    OR
    
    SELECT Name, 
     case
       when Answer=1 then '喜歡'
       when Answer=2 then '不喜歡'
       when Answer=3 then '一般'
      END AS Answer
      FROM table;
  

【MySQL】LIKE 搜尋

一、一般

1.name LIKE '豬%' -> 找豬開頭的文字後面不論字數，例如:豬八戒、豬頭
2.name LIKE '%豬%' -> 只有出現豬就算找到，如豬、神豬、豬頭
3.name LIKE '豬_' -> 限定字數只找豬開頭兩個字，底線在那邊就一定要有字
4.name LIKE '%豬%戒%' -> 只要字串包含順序有豬跟界就符合

二、應用搜尋

// 將字切開並加上%
$arr = $this->utf8_str_split($keywords);
$keywordSplit = implode('%',$arr);

// 這樣比 '%$keywords%' 搜尋到更多結果
$sql = "SELECT * FROM user WHERE name LIKE '%$keywordSplit%'";  
$rs = $pdo->query($sql);

// 用UTF8切文字  
function utf8_str_split($str, $split_len = 1){
 if (!preg_match('/^[0-9]+$/', $split_len) || $split_len < 1)
   return FALSE;

 $len = mb_strlen($str, 'UTF-8');
 if ($len <= $split_len)
   return array($str);

 preg_match_all('/.{'.$split_len.'}|[^\x00]{1,'.$split_len.'}$/us', $str, $ar);

 return $ar[0];
}  
  

【SQL】優化搜尋

今天發現網站變慢，查了一下原因

1.量大需限定範圍:

資料大約50萬筆，抓最近5筆資料，多加了範圍限制快了10倍

$sql = "SELECT TOP(5) *
FROM page_log pl 
ORDER BY time DESC";

$s = microtime(time);
$result = $this->db->queryAll($sql);
$e = microtime(time);
echo $e - $s;

結果：0.605秒

$sql = "SELECT TOP(5) *
FROM page_log pl 
WHERE time > '20140807000000'
ORDER BY time DESC";

$s = microtime(time);
$result = $this->db->queryAll($sql);
$e = microtime(time);
echo $e - $s;

結果：0.07秒

【MySQL】 預設儲存引擎: InnoDB 介紹

文章出處：小惡魔 – 電腦技術 – 工作筆記 – AppleBOY


MySQL 是一套眾所皆知的 Database System，今天來簡介 InnoDB 儲存引擎，在 MySQL 5.5.5 之前預設的儲存引擎是 MyISAM，但是為什麼在 5.5 之後官方要將預設儲存引擎換成 InnoDB 呢？大家都知道 InnoDB 用來交易管理非常方便，因為 InnoDB 透過 row lock，相對於 MyISAM 透過 table lock 來的有效率，也避免大量寫入的時候，造成無法讀取資料，這就是 row lock 的優勢，當然用 InnoDB 最主要的原因還有 Full-text search indexes 功能，但是別擔心 MySQL 5.6.4 之後(含此版本) InnoDB 開始支援 Full-text search 功能，另外在使用 MyISAM 時候，如果資料突然出問題，還必須使用 MySQL Binary Log 來恢復資料，如果用 InnoDB 這就沒關係了。大家還在用 MyISAM 嘛？開始升級伺服器，一起體驗 InnoDB 的功能，如果已經上線很久的網站，作者不建議轉換，因為可能會遇到很多雷。 目前不用太擔心硬體的架構這方面了，隨便都是 64G 記憶體，四核心主機，大家所在意的還是 MySQL 是否 reliability 跟錯誤恢復，所以 MySQL 在 5.5 以後的版本大膽將 InnoDB 儲存引擎，建立資料表不用再加上 ENGINE=InnoDB，但是大家可以發現 MySQL 安裝好後，內建 mysql 和information_schema 資料庫還是用 InnoDB 儲存引擎，請大家不要亂動這兩個資料庫。底下來看看 InnoDB 的優勢。

InnoDB 優勢

已經使用過 InnoDB 的朋友們，作者相信你可以來嘗試看看 InnoDB，使用的同時會發現很多 InnoDB 優點。 1. 如果伺服器因為硬體或軟體疏失，無論發生任何問題，請重新啟動伺服器，啟動之後並不需要做任何事情，InnoDB 會自動修復 crash 部份，將已經 commit 的資料全部寫回資料表。假如您處理任何資料，但是尚未 commit，系統會自動恢復，所以只要將伺服器重新啟動，就可以恢復到 crash 之前的狀態。 2. InnoDB 將 Table 及 index 資料 cache 在 buffer pool，所以可以快速存取任何資料，因為這些資料都是直接從 Memory 讀取，快取可以存放任和型態的資料，提升處理效能，假如您有實體主機，請設定 60% ~ 80% 實體記憶體給 InnoDB buffer pool。 3. 設計資料庫時，請務必在每個資料表設定適當的 Primary key，當您在執行任何 SQL 語法時，只要牽扯到 Primary key，InnoDB 會自動優化效能，如果將 Primary key 用在 WHERE，ORDER BY，GROUP 等條件子句或 join 操作，讀取速度會是非常快。 4. InnoDB 可以讓您同時讀取或寫入同一個資料表，它將需要改變的資料存在 streamline disk I/O。所以大家不用擔心 Lock Table 的問題了。 5. InnoDB 提供錯誤偵測 (checksum mechanism)，假如有資料已經損壞在 Disk 或 Memory，使用此資料之前，系統將會提醒你。 6. InnoDB 具備處理極大量資料的效能優勢，假如在同一資料表存取同樣的資料，內部透過 Adaptive Hash Index 機制提升讀取速度。 以上是作者覺得 InnoDB 改善的地方，大家可以透過 SHOW ENGINES 指令知道伺服器是否支援 InnoDB

【MySQL、MSSQL】 分頁寫法

MSSQL 分頁的寫法:
抓取第80000~80020的資料 

SELECT * FROM
(SELECT ROW_NUMBER() over (order by id ASC) sort,* FROM test) as c
WHERE sort BETWEEN 80000 and 80020

OR

WITH Table AS (
   SELECT ROW_NUMBER() over (order by id ASC) sort,* FROM test
) SELECT * FROM Table WHERE sort BETWEEN 80000 and 80020



MYSQL：

SELECT * FROM `test` LIMIT 80000,20


* MySQL 簡單多了

【MYSQL、MSSQL】複製資料表、複製一筆資料

1.複製一個資料表
MySQL 建議使用下面的方式，好處是 Schema 會一樣

1. CREATE TABLE new_table LIKE old_table;
2. INSERT new_table SELECT * FROM old_table

MSSQL 要使用 SELECT * INTO new_table FROM old_table
只複製結構 SELECT * INTO new_table FROM old_table WHERE 1=0

2.複製一筆資料
MySQL、MSSQL 都可使用

INSERT INTO test(
id,
        name,
test1,
test2
)
SELECT
3 AS id,
name,
'test' AS test1,
test2
FROM test
WHERE id =1

* 要複製原來的值就不用AS

3.運用
當資料量大，查詢時可將一個表拆成多個表
如:
Insert Into new_table （學號, 姓名,國文,英文,數學）
Select * From old_table Where 英文>90

【MySQL】sql 的 if 及switch

-- switch 的用法 SELECT CASE col WHEN 100 THEN '1' WHEN 50 THEN '2' ELSE '3' END FROM table; -- if else 的用法 SELECT CASE WHEN col>100 THEN '1' WHEN col>50 THEN '2' ELSE '3' END FROM table;

參考:http://jax-work-archive.blogspot.tw/2008/06/case-mysql-switch-if-else.html

【mysql】不常用語法

1. is Null
SELECT * FROM user WHERE address is Null

2.is not Null
SELECT * FROM user WHERE address is not Null

3.not like
SELECT * FROM user WHERE address not like '_中%'

4.view 建立及移除暫存資料表
create view tmp as SELECT * FROM user WHERE address is Null
drop view tmp

5.distinct 重複的不計
SELECT count(distinct address) FROM user WHERE address is Null

【mysql】忘記密碼

【MYSQL】判斷欄位是否存在

$result = db_query("DESCRIBE imw_meeting");
while($rs = db_fetch_object($result)){
 $row[] = $rs->Field;
}

// 是否有我要的欄位
if (!in_array('proxy_user_id',$row)){
 db_query("ALTER TABLE imw_meeting ADD proxy_user_id INT NOT NULL AFTER user_id;");
}
if (!in_array('proxy_user_type_id',$row)){
 db_query("ALTER TABLE imw_meeting ADD proxy_user_type_id INT NOT NULL AFTER proxy_user_id;");
}

【MYSQL】exceeded the 'max_questions' resource

當出現User 'user' has exceeded the 'max_questions'
resource (current value: 1000)時，表示這個使用者的查詢次數超過資料庫中設定的上限(此例為1000筆)，這項限制似乎只有在一次做這麼大量處理的時候會受限，例如用BCB中的BatchMove一次搬超過1000筆資料就會跳出這個錯誤，解決的方法是修改該使用者的max_questions這項參數，方法有二，一是用Navicat這類的工具，用root登入，打開MySQL這個database，打開user這個table，把需要修改的user的max_questions加大或乾脆改成0，第二個方法是用root登入MySQL的console模式下指令，指令如下：

mysql> use mysql (切換到mysql資料庫)
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select user, max_questions from user; (看看各user的max_questions)
+------------------+---------------+
| user             | max_questions |
+------------------+---------------+
| root             |             0 |
| root             |             0 |
| debian-sys-maint |             0 |
| root             |             0 |
| user             |             1000 |
+------------------+---------------+
5 rows in set (0.00 sec)

mysql> update user set max_questions = 0 where user = 'user'; (把max_questions改成0)
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0


mysql> flush privileges; (讓修改立即生效)
Query OK, 0 rows affected (0.00 sec)

出處:http://blog.xuite.net/flamejoker/blog/34528784-%E5%82%99%E5%BF%98+-+exceeded+the+'max_questions'+resource

【linux】mysql設定

看到一篇有關linux mysql詳細設定，就把他記下來
http://la.shsps.kh.edu.tw/spcase/centos/mysql.html


安裝
yum -y install mysql-server php-mysql

啟動
service mysqld start

設定密碼
mysqladmin -u root password 'password'

設定utf8萬國碼，新增以下兩個敘述句
vi /etc/my.cnf
         [mysqld]
         max_allowed_packet = 2M
         max_connections = 1000
         default-character-set=utf8 (注意:在CentOS6裡， 這裡應該是character-set-server =utf8)
         init_connect='SET NAMES utf8'

[client]
default-character-set=utf8
忘記密碼（沒有重要資料）
service mysqld stop
rm -rf /var/lib/mysql
service mysqld start
修改密碼
mysqladmin password 新密碼
防火牆設定，限定使用來源
vi /etc/sysconfig/iptables
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp -s 192.168.10.0/24 --dport 3306 -j ACCEPT（網段）
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp -s 192.168.10.1 --dport 3306 -j ACCEPT（單機）
管理者登入
mysql -u root -p
資料庫備份指令
mysqldump -uroot -p -A --default-character-set=utf8 > mysql.sql（全部資料庫）
mysqldump -uroot -p -a --default-character-set=utf8 school > school.sql（單筆資料庫school）
mysql -uroot -p < database.sql （全部資料庫還原）
mysql --user=root -p school < /var/www/html/mmysql.sql（單筆資料庫還原school）

【重新安裝 MySQL】
service mysqld stop
yum -y remove mysql*
rm -rf /var/lib/mysql
yum -y install php* mysql* mysql-server php-mysql php-gd
/etc/rc.d/init.d/mysqld start

如果啟動異常
嘗試一
touch /var/lib/mysql/mysql.sock
chown mysql:mysql /var/lib/mysql/mysql.sock
chmod 4777 /var/lib/mysql/mysql.sock
還是沒有啟用成功，再restore your socket
mysqld_safe --user=mysql &

嘗試二
mysql_install_db
設定密碼
mysqladmin -u root password '密碼'