2018年9月27日星期四

Basic

StackOverflow

Datetime 基本上就是儲存某個時間，通常不會改變。
Timestamp 基本上用來當作戳記，也就是會改變。當你改資料，會一起改timestamp。

Datetime

存入死的日期，就算更改mysql timezone，資料也不會變。

Timestamp

Save：current time zone converted to UTC for storage.
Retrieval: converted from UTC to current time zone for retrieval.
Timestamp存入的是UTC時間，取出後，會根據mysql time zone轉成當地時間。

兩者比較

處理速度大部份時候比datetime快。
timestamp有cache，datetime沒有。
timestamp有max 2038年問題。

Defult Value

官方說明

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);


CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT 0,
  dt DATETIME DEFAULT 0
);

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE t1 (
  ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,     -- default 0
  ts2 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL
);

Eg.

mysql> show variables like '%time_zone%';
+------------------+---------------------+
| Variable_name    | Value               |
+------------------+---------------------+
| system_time_zone | India Standard Time |
| time_zone        | Asia/Calcutta       |
+------------------+---------------------+

mysql> create table datedemo(
    -> mydatetime datetime,
    -> mytimestamp timestamp
    -> );

mysql> insert into datedemo values ((now()),(now()));

mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 14:11:09 |
+---------------------+---------------------+

mysql> set time_zone="america/new_york";

mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 04:41:09 |
+---------------------+---------------------+

2018年9月24日星期一

KEY

Key 都會自動建立index
Key可以說是index加上某種限制

PRIMARY KEY

Only one PRIMARY KEY in a table
Value must be unique
Value can not be null
Indexing auto
Primary key允許欄位有null

UNIQUE KEY

Value of column must be unique
Can be null
Indexing auto
Unique key允許欄位有null

CREATE TABLE table_name(
	//...
   UNIQUE KEY(index_column_,index_column_2,...) 
);

//set unique index
CREATE UNIQUE INDEX index_name ON table_name(index_column_1,index_column_2,...);

FOREIGN KEY

參考

Indexing auto
Only references to PRIMARY KEY in another table (NO)

Parameter:
ON DELETE {CASCADE | SET NULL | NO ACTION}
ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}

Eg.

CREATE TABLE item (
id INT AUTO_INCREMENT,
PRIMARY KEY(id),
user_id INT NOT NULL,
FOREIGN KEY(user_id) REFERENCES user(id) ON DELETE CASCADE ON UPDATE CASCADE);

條件

使用innodb

Eg.

CREATE TABLE product (
prod_id INT NOT NULL,
prod_name CHAR(20),
PRIMARY KEY (prod_id))
ENGINE = INNODB;

CREATE TABLE order (
order_id INT NOT NULL,
prod_id INT,
FOREIGN KEY(prod_id) REFERENCES product(prod_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
PRIMARY KEY (order_id))
ENGINE = INNODB;

設定 foreign key
order table 的 prod_id 為foreign key

設定參數
ON DELETE CASCADE
ON UPDATE CASCADE

效果
此時如果 delete product table的某筆資料，
也會 delete order 有foreign key參考到的資料。

此時如果update product table的某筆 prod_id，
也會 update order 有foreign key參考到的 prod_id。

此時drop product table 會出錯，因為 order table 參考到它。

FOREIGN KEY 參數說明

[ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
[ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]

當關聯父資料表的主鍵紀錄行被刪除或修改時，InnoDB 對子資料表中紀錄行的處理方式：

CASCADE - 會將有所關聯的紀錄行也會進行刪除或修改。
SET NULL - 會將有所關聯的紀錄行設定成 NULL。
NO ACTION - 有存在的關聯紀錄行時，會禁止父資料表的刪除或修改動作。
RESTRICT - 與 NO ACTION 相同。

FOREIGN KEY效用

利用 db schema 防止程式錯誤及阻擋不合法寫入。
加速 join 效率。

INDEX

參考
Create a indexing data in another table for searching faster.
Index 允許欄位有null
Index 不必要是唯一值

Eg.

CREATE TABLE index_sample(
id INT,
index id_idx(id));

Eg.1

CREATE INDEX index＿name ON table_name(column_name(length));

If column is CHAR, VARCHAR type, lenght can less then real length.
If is BLOB or TEXT ,must have length assigned.

Eg.2

ALTER table_name ADD INDEX index_name ON column_name(length))

組合索引

把常用的幾個一起查詢的欄位，建立成一個組合索引，效率更好。

CREATE TABLE my_table(  
id INT NOT NULL,   
user_name VARCHAR(16) NOT NULL,  
city VARCHAR(50) NOT NULL,  
age INT NOT NULL 
);  

//三個欄位建立組合索引
ALTER TABLE my_table ADD INDEX name_city_age (uer_name(10),city,age);

等於建立以下三個索引
user_name, city, age
user_name, city
user_name
因為索引組合是從最左邊開始的。

以下查詢會用到這個組合索引

SELECT * FROM my_table WHERE user_name="name" AND city="zz"
SELECT * FROM my_table WHERE user_name="abc"

以下查詢不會用到這個組合索引

SELECT * FROM my_table WHERE age=10 AND city="zz"
SELECT * FROM my_table WHERE city="qqq"

INDEX使用時機

Mysql 在以下時機使用

//在 WHERE 中使用
//在 JOIN 中使用
< 
<=
=
>
>=
BETWEEN
IN
及某些LIKE
LIKE "%qee"，用％開頭的LIKE不會用索引
LIKE "qee%"這樣會用

以下要對city 及 age建立索引，
my_table的user_name也要。

SELECT t.Name 
FROM my_table t LEFT JOIN my_table m   
ON t.Name=m.user_name WHERE m.age=20 AND m.city="xxx"

索引的缺點

索引加快搜尋速度，降低更改速度。因為INSERT, UPDATE, DELETE也要更新索引。
索引會佔用一點空間。

使用索引注意事項，以下不會使用索引

當mysql優化器判斷全表掃描比索引快
索引不會包含有NULL的欄位
多使用短索引
不鼓勵使用LIKE
不要在SQL語句上計算
不要在SQL語句上使用函數運算

//這樣有計算
select * from users where YEAR(adddate)

//改成這樣
select * from users where adddate<‘2007-01-01’

不使用NOT IN 和 <>
類型錯誤

//假設 X type 是 char
select * from users where X =1

//改成這樣
select * from users where X ='1'

強制使用索引

force index 索引名

優化

my.cnf 中的 key_buffer 值能夠把所有索引容納進去最好
SQL 指令前加上 explain 來顯示該 SQL 是否用到索引

2018年9月18日星期二

MySQL 8.0 更改為native password 及忘記密碼重設

更改為native password

[mysqld]
default-authentication-plugin=mysql_native_password

新的
default-authentication-plugin=caching_sha2_password

這樣子沒用，會造成原本的root無法登入。

mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'password';

移除password validate

UNINSTALL COMPONENT 'file://component_validate_password';

重設密碼

官方

Stop the MySQL server if necessary, then restart it with the --skip-grant-tables

shell> mysql

mysql> FLUSH PRIVILEGES;

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

2018年9月17日星期一

Homebrew

Command

Package

Package installed Path

/user/local/Cellar/

List installed package

brew list

List target package

brew list mysql

Install package of latest version

brew install mysql

Install package of target version

brew install mysql@5.6

Unlink now package

brew unlink mysql@5.6

Switch to new package

brew switch <formula><version>

brew switch mysql 8.0.12

Check current linked package

ls /usr/local/Cellar/<package>
ls /usr/local/Cellar/mysql

List which version of package is currently symlinked

ls -l /usr/local/bin/msyql => ../Cellar/mysql56/5.6.27/bin/mysql

Service

Install brew services

brew tab homebrew/services

Start/stop a service

sudo brew services start mysql
sudo brew services stop mysql

is equal to:
* ln -sfv /usr/local/opt/mysql/*.plist ~/Library/LaunchAgents
* launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

List installed services

brew services list

Run but not a background service

mysql.server start

List services

brew services list

MySQL

homebrew github

裝多份mysql 出現 connect /tmp/mysql.sock error

裝多個版本

Start mysql as service

sudo brew services start mysql
sudo brew services stop mysql

Run but not a background service

mysql.server start

重複裝出現connect /tmp/mysql.sock error，

先把之前的data folder 刪掉，
unlink，移除再重裝(或 reinstall)

2018年9月10日星期一

Goland mac delve problem

Debug時錯誤

Error：could not launch process: EOF
Reason：goland使用內建的dlv，版本太舊，改用新的就可以了。

1 brew安裝delve

https://github.com/go-delve/homebrew-delve

2 goland 指定使用自己安裝的delve

選單 Help / Edit custom properties...
dlv.path=/Users/cruise/go/bin/dlv

2018年9月27日 星期四

Basic

Datetime

Timestamp

兩者比較

Defult Value

2018年9月24日 星期一

KEY

PRIMARY KEY

UNIQUE KEY

FOREIGN KEY

條件

Eg.

FOREIGN KEY 參數說明

FOREIGN KEY效用

INDEX

組合索引

INDEX使用時機

索引的缺點

使用索引注意事項，以下不會使用索引

強制使用索引

優化

2018年9月18日 星期二

更改為native password

移除password validate

重設密碼

2018年9月17日 星期一

Command

Package

Package installed Path

List installed package

List target package

Install package of latest version

Install package of target version

Unlink now package

Switch to new package

Check current linked package

List which version of package is currently symlinked

Service

Install brew services

Start/stop a service

List installed services

Run but not a background service

List services

MySQL

Start mysql as service

Run but not a background service

重複裝 出現connect /tmp/mysql.sock error，

2018年9月10日 星期一

Debug時錯誤

1 brew安裝delve

2 goland 指定使用自己安裝的delve

PrettyPrint

2018年9月27日星期四

2018年9月24日星期一

2018年9月18日星期二

2018年9月17日星期一

重複裝出現connect /tmp/mysql.sock error，

2018年9月10日星期一