MySQL 快速匯入 CSV 檔案 – 使用 LOAD DATA

Posted on 2017-04-12 by hoyo

直接匯入 CSV 檔案到 MySQL 資料庫

—

實際範例

LOAD DATA LOCAL INFILE '/tmp/a.csv' INTO TABLE UserTemp FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES ( id, Name, position, `group`, recommend, IDNumber, Email, phone, city, province, JoinDate, ServerNo, status, status2 );

1	LOAD DATA LOCAL INFILE '/tmp/a.csv' INTO TABLE UserTemp FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES ( id, Name, position, `group`, recommend, IDNumber, Email, phone, city, province, JoinDate, ServerNo, status, status2 );

需要注意指令順序

—

效能

如果可以使用 LOAD DATA 一定會比使用程式拆解後逐筆插入快，五萬筆的匯入在小白 MacBook 上也只需不到 3 秒

mysql> LOAD DATA LOCAL INFILE '/tmp/b.csv' INTO TABLE UserTemp FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES ( id, Name, position, `group`, recommend, IDNumber, Email, phone, city, province, JoinDate, ServerNo, status, status2 );
Query OK, 57378 rows affected, 3 warnings (2.75 sec)
Records: 57378  Deleted: 0  Skipped: 0  Warnings: 0

mysql> LOAD DATA LOCAL INFILE '/tmp/b.csv' INTO TABLE UserTemp FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES ( id, Name, position, `group`, recommend, IDNumber, Email, phone, city, province, JoinDate, ServerNo, status, status2 );

Query OK, 57378 rows affected, 3 warnings (2.75 sec)

Records: 57378 Deleted: 0 Skipped: 0 Warnings: 0

—

ERROR 1045 (28000): Access denied for user ‘XXXXX’@’%’ (using password: YES)

access denied for load data infile in MySQL

將 LOAD DATA INFILE 改成 LOAD DATA LOCAL INFILE

—

MySQL Partitioning 分區

Posted on 2016-05-30 by hoyo

—

限制

Restrictions and Limitations on Partitioning
分區資料表就不支援 Foreign key

—

MySQL 分區可以設定更改原先資料儲存結果。

以 MYISAM 引擎為例，原本一個 A 資料表在硬碟會存在

A.frm
A.MYD
A.MYI

三個檔案，無論資料筆數多寡都放在同一檔案內，這時資料儲存限制 == 作業系統單一檔案大小限制。在目前主流檔案系統之下，單一檔案大小限制在 4G ~ 16TByte 之間。

資料表太大對資料庫不是好事，有可能只為了讀取 10Byte 的資料，卻將整個資料表都讀取一遍，如果可能還是需要控制資料表的實體儲存容量，達到增進效能以及方便管理的目的。

參考

CREATE TABLE part_tab ( 
    c1 int default NULL,
    c2 varchar(30) default NULL,
    c3 date default NULL
) engine=myisam
PARTITION BY RANGE (year(c3)) (
    PARTITION p0 VALUES LESS THAN (1995),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (1997),
    PARTITION p3 VALUES LESS THAN (1998),
    PARTITION p4 VALUES LESS THAN (1999),
    PARTITION p5 VALUES LESS THAN (2000),
    PARTITION p6 VALUES LESS THAN (2001),
    PARTITION p7 VALUES LESS THAN (2002),
    PARTITION p8 VALUES LESS THAN (2003),
    PARTITION p9 VALUES LESS THAN (2004),
    PARTITION p10 VALUES LESS THAN (2010),
    PARTITION p11 VALUES LESS THAN MAXVALUE
);

CREATE TABLE part_tab (

c1 int default NULL,

c2 varchar(30) default NULL,

c3 date default NULL

) engine=myisam

PARTITION BY RANGE (year(c3)) (

PARTITION p0 VALUES LESS THAN (1995),

PARTITION p1 VALUES LESS THAN (1996),

PARTITION p2 VALUES LESS THAN (1997),

PARTITION p3 VALUES LESS THAN (1998),

PARTITION p4 VALUES LESS THAN (1999),

PARTITION p5 VALUES LESS THAN (2000),

PARTITION p6 VALUES LESS THAN (2001),

PARTITION p7 VALUES LESS THAN (2002),

PARTITION p8 VALUES LESS THAN (2003),

PARTITION p9 VALUES LESS THAN (2004),

PARTITION p10 VALUES LESS THAN (2010),

PARTITION p11 VALUES LESS THAN MAXVALUE

);

以時間作為分區依據

以一個有 AUTO_INCREMENT 及 CURRENT_TIMESTAMP 的資料表為例，

關鍵是「必須把區分時間的欄位包含到 Primary Key 內」

CREATE TABLE IF NOT EXISTS `NODE1p` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `V0` float NOT NULL DEFAULT '0',
  `V1` float NOT NULL DEFAULT '0',
  `V2` float NOT NULL DEFAULT '0',
  `V3` float NOT NULL DEFAULT '0',
  `V4` float NOT NULL DEFAULT '0',
  `V5` float NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`, `time`)
) ENGINE=myisam DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
PARTITION BY RANGE (UNIX_TIMESTAMP(`time`)) (
    PARTITION p2014 VALUES LESS THAN (UNIX_TIMESTAMP('2015-01-01 00:00:00')),
    PARTITION p2015 VALUES LESS THAN (UNIX_TIMESTAMP('2016-01-01 00:00:00')),
    PARTITION pNOW VALUES LESS THAN MAXVALUE
);

CREATE TABLE IF NOT EXISTS `NODE1p` (

`id` int(255) NOT NULL AUTO_INCREMENT,

`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

`V0` float NOT NULL DEFAULT '0',

`V1` float NOT NULL DEFAULT '0',

`V2` float NOT NULL DEFAULT '0',

`V3` float NOT NULL DEFAULT '0',

`V4` float NOT NULL DEFAULT '0',

`V5` float NOT NULL DEFAULT '0',

PRIMARY KEY (`id`, `time`)

) ENGINE=myisam DEFAULT CHARSET=utf8 AUTO_INCREMENT=1

PARTITION BY RANGE (UNIX_TIMESTAMP(`time`)) (

PARTITION p2014 VALUES LESS THAN (UNIX_TIMESTAMP('2015-01-01 00:00:00')),

PARTITION p2015 VALUES LESS THAN (UNIX_TIMESTAMP('2016-01-01 00:00:00')),

PARTITION pNOW VALUES LESS THAN MAXVALUE

);

INSERT 新的分區資料表 SELECT * FROM 原資料表;

1	INSERT 新的分區資料表 SELECT * FROM 原資料表;

—

一個將無分區資料表修改為具分區功能範例

建立一個具有分區功能資料表，必須和原資料表資料結構相同，準備做替換用
將原資料表資料複製到此分區資料表，使用的是 INSERT SELECT 語法
刪除原資料表
將分區資料表名稱更換為原資料表名稱

CREATE TABLE IF NOT EXISTS `NODE1p` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `V0` float NOT NULL DEFAULT '0',
  `V1` float NOT NULL DEFAULT '0',
  `V2` float NOT NULL DEFAULT '0',
  `V3` float NOT NULL DEFAULT '0',
  `V4` float NOT NULL DEFAULT '0',
  `V5` float NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`, `time`)
) ENGINE=myisam DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
PARTITION BY RANGE (UNIX_TIMESTAMP(`time`)) (
    PARTITION p2014 VALUES LESS THAN (UNIX_TIMESTAMP('2015-01-01 00:00:00')),
    PARTITION p2015 VALUES LESS THAN (UNIX_TIMESTAMP('2016-01-01 00:00:00')),
    PARTITION p2016 VALUES LESS THAN (UNIX_TIMESTAMP('2017-01-01 00:00:00')),
    PARTITION p2017 VALUES LESS THAN (UNIX_TIMESTAMP('2018-01-01 00:00:00')),
    PARTITION p2018 VALUES LESS THAN (UNIX_TIMESTAMP('2019-01-01 00:00:00')),
    PARTITION p2019 VALUES LESS THAN (UNIX_TIMESTAMP('2020-01-01 00:00:00')),
    PARTITION p2020 VALUES LESS THAN (UNIX_TIMESTAMP('2021-01-01 00:00:00')),
    PARTITION p2021 VALUES LESS THAN (UNIX_TIMESTAMP('2022-01-01 00:00:00')),
    PARTITION p2022 VALUES LESS THAN (UNIX_TIMESTAMP('2023-01-01 00:00:00')),
    PARTITION p2023 VALUES LESS THAN (UNIX_TIMESTAMP('2024-01-01 00:00:00')),
    PARTITION pNOW VALUES LESS THAN MAXVALUE
);

INSERT `NODE1p` SELECT * FROM `NODE1`;
DROP TABLE `NODE1`;
RENAME TABLE `NODE1p` TO `NODE1` ;

CREATE TABLE IF NOT EXISTS `NODE1p` (

`id` int(255) NOT NULL AUTO_INCREMENT,

`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

`V0` float NOT NULL DEFAULT '0',

`V1` float NOT NULL DEFAULT '0',

`V2` float NOT NULL DEFAULT '0',

`V3` float NOT NULL DEFAULT '0',

`V4` float NOT NULL DEFAULT '0',

`V5` float NOT NULL DEFAULT '0',

PRIMARY KEY (`id`, `time`)

) ENGINE=myisam DEFAULT CHARSET=utf8 AUTO_INCREMENT=1

PARTITION BY RANGE (UNIX_TIMESTAMP(`time`)) (

PARTITION p2014 VALUES LESS THAN (UNIX_TIMESTAMP('2015-01-01 00:00:00')),

PARTITION p2015 VALUES LESS THAN (UNIX_TIMESTAMP('2016-01-01 00:00:00')),

PARTITION p2016 VALUES LESS THAN (UNIX_TIMESTAMP('2017-01-01 00:00:00')),

PARTITION p2017 VALUES LESS THAN (UNIX_TIMESTAMP('2018-01-01 00:00:00')),

PARTITION p2018 VALUES LESS THAN (UNIX_TIMESTAMP('2019-01-01 00:00:00')),

PARTITION p2019 VALUES LESS THAN (UNIX_TIMESTAMP('2020-01-01 00:00:00')),

PARTITION p2020 VALUES LESS THAN (UNIX_TIMESTAMP('2021-01-01 00:00:00')),

PARTITION p2021 VALUES LESS THAN (UNIX_TIMESTAMP('2022-01-01 00:00:00')),

PARTITION p2022 VALUES LESS THAN (UNIX_TIMESTAMP('2023-01-01 00:00:00')),

PARTITION p2023 VALUES LESS THAN (UNIX_TIMESTAMP('2024-01-01 00:00:00')),

PARTITION pNOW VALUES LESS THAN MAXVALUE

);

INSERT `NODE1p` SELECT * FROM `NODE1`;

DROP TABLE `NODE1`;

RENAME TABLE `NODE1p` TO `NODE1` ;

—

顯示資料表分區資訊

使用的是顯示建立資料表資訊技巧

SHOW CREATE TABLE

1	SHOW CREATE TABLE

—

一個分區資料表建立後修改範例

CREATE TABLE `NODE2p` (
 `id` int(255) NOT NULL AUTO_INCREMENT,
 `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `V0` float NOT NULL DEFAULT '0',
 `V1` float NOT NULL DEFAULT '0',
 `V2` float NOT NULL DEFAULT '0',
 `V3` float NOT NULL DEFAULT '0',
 `V4` float NOT NULL DEFAULT '0',
 `V5` float NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`,`time`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (UNIX_TIMESTAMP(`time`))
(PARTITION p2014 VALUES LESS THAN (1388505600) ENGINE = MyISAM,
PARTITION p2015 VALUES LESS THAN (1420041600) ENGINE = MyISAM,
PARTITION pNOW VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */

CREATE TABLE `NODE2p` (

`id` int(255) NOT NULL AUTO_INCREMENT,

`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

`V0` float NOT NULL DEFAULT '0',

`V1` float NOT NULL DEFAULT '0',

`V2` float NOT NULL DEFAULT '0',

`V3` float NOT NULL DEFAULT '0',

`V4` float NOT NULL DEFAULT '0',

`V5` float NOT NULL DEFAULT '0',

PRIMARY KEY (`id`,`time`)

) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

/*!50100 PARTITION BY RANGE (UNIX_TIMESTAMP(`time`))

(PARTITION p2014 VALUES LESS THAN (1388505600) ENGINE = MyISAM,

PARTITION p2015 VALUES LESS THAN (1420041600) ENGINE = MyISAM,

PARTITION pNOW VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */

ALTER TABLE NODE2p REORGANIZE PARTITION pNOW INTO (
    PARTITION p201601 VALUES LESS THAN (UNIX_TIMESTAMP('2016-02-01 00:00:00')),
    PARTITION p201602 VALUES LESS THAN (UNIX_TIMESTAMP('2016-03-01 00:00:00')),
    PARTITION pNOW VALUES LESS THAN MAXVALUE
);

ALTER TABLE NODE2p REORGANIZE PARTITION pNOW INTO (

PARTITION p201601 VALUES LESS THAN (UNIX_TIMESTAMP('2016-02-01 00:00:00')),

PARTITION p201602 VALUES LESS THAN (UNIX_TIMESTAMP('2016-03-01 00:00:00')),

PARTITION pNOW VALUES LESS THAN MAXVALUE

);

—

分區區分到年、月

MySQL table partition by month

CREATE TABLE IF NOT EXISTS `J` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `Create_Time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
  `AuctionDate` date NOT NULL,
  `AuctionNumber` int(11) NOT NULL,
  PRIMARY KEY (`id`, `AuctionDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
PARTITION BY RANGE((YEAR(`AuctionDate`) * 100) + MONTH(`AuctionDate`))(
    PARTITION p199501 VALUES LESS THAN (199501),
    PARTITION p199502 VALUES LESS THAN (199502)
);

CREATE TABLE IF NOT EXISTS `J` (

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',

`Create_Time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',

`AuctionDate` date NOT NULL,

`AuctionNumber` int(11) NOT NULL,

PRIMARY KEY (`id`, `AuctionDate`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1

PARTITION BY RANGE((YEAR(`AuctionDate`) * 100) + MONTH(`AuctionDate`))(

PARTITION p199501 VALUES LESS THAN (199501),

PARTITION p199502 VALUES LESS THAN (199502)

);

順便說一個情況，第一個設定是 199501 ，代表 1995年1月以前的資料存在此，而199502 則只存放 1995年 2 月的資料，因為沒有 MAXVALUE 所以超過的日期會新增失敗

—

InnoDB 如何分區區分檔案

vi /etc/my.cnf

[mysqld]
innodb_file_per_table=1

vi /etc/my.cnf

[mysqld]

innodb_file_per_table=1

重新啟動資料庫

—

使用 HASH

HASH Partitioning

CREATE TABLE IF NOT EXISTS `Data2` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `Create_Time` datetime NOT NULL COMMENT '建立時間',
  `Device_id` int(11) NOT NULL,
  `Key` enum('Temperature1','Humidity1','Watt1','WalkStep1') NOT NULL,
  `Value` decimal(24,12) NOT NULL,
  `is_Del` enum('Y','N') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N' COMMENT '是否刪除',
  UNIQUE KEY `id` (`id`,`Create_Time`),
  KEY `Device_id` (`Device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `Data2` (

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',

`Create_Time` datetime NOT NULL COMMENT '建立時間',

`Device_id` int(11) NOT NULL,

`Key` enum('Temperature1','Humidity1','Watt1','WalkStep1') NOT NULL,

`Value` decimal(24,12) NOT NULL,

`is_Del` enum('Y','N') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N' COMMENT '是否刪除',

UNIQUE KEY `id` (`id`,`Create_Time`),

KEY `Device_id` (`Device_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

使用 Create_Time 轉天數後 hash

ALTER TABLE `Data2` PARTITION BY hash (TO_DAYS(Create_Time)) partitions 31;

1	ALTER TABLE `Data2` PARTITION BY hash (TO_DAYS(Create_Time)) partitions 31;

SELECT TO_DAYS( '0001-01-01' );
-> 366

1 2	SELECT TO_DAYS( '0001-01-01' ); -> 366

從插入多筆 2018/06/07 資料來觀察 HASH 儲存資料邏輯

SELECT TO_DAYS('2018-06-07');
-> 737217

SELECT MOD(737217,31);
-> 6

SELECT TO_DAYS('2018-06-07');

-> 737217

SELECT MOD(737217,31);

-> 6

—

分區刪除合併檔案變化

分區會將檔案根據條件分開，刪除分區檔案就會合併？

理論上是如此，實際測試亦是如此

—

查詢時是否有效套用到分區？

使用 EXPLAIN PARTITIONS 可以查詢取用分區檔案的狀態

EXPLAIN PARTITIONS SELECT * 
FROM  `Data2` 
WHERE Create_Time =  '2018-06-07'

EXPLAIN PARTITIONS SELECT *

FROM `Data2`

WHERE Create_Time = '2018-06-07'

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Data2	p6	ALL	NULL	NULL	NULL	NULL	2738	Using where

如果查訊跨到多日就會查詢到多個或是全部的分區檔案，如此就失去分區作用

—

PDO 長連接 PDO::ATTR_PERSISTENT 測試及了解

Posted on 2016-04-26 by hoyo

PHP PDO MySQL 長連結設定及影響

設定短連接，一次資料庫查詢程式

$PDO = new PDO( PDO_DSN, MYSQL_USERNAME, MYSQL_PASSWORD, array(
    PDO::ATTR_PERSISTENT => false
) );
$PDO->exec("set names utf8");

$sql = " SELECT * FROM UserLog  ";
$pre = $PDO->prepare($sql);
$pre->execute();

$PDO = new PDO( PDO_DSN, MYSQL_USERNAME, MYSQL_PASSWORD, array(

PDO::ATTR_PERSISTENT => false

) );

$PDO->exec("set names utf8");

$sql = " SELECT * FROM UserLog ";

$pre = $PDO->prepare($sql);

$pre->execute();

短連接，一次資料庫查詢結果

"2016-04-26 09:34:55","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",86,0,"Connect","MJAPP_SoundU@localhost on MJAPP_SoundU2"
"2016-04-26 09:34:55","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",86,0,"Query","set names utf8"
"2016-04-26 09:34:55","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",86,0,"Query","SET GLOBAL time_zone='+8:00'"
"2016-04-26 09:34:55","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",86,0,"Query","SELECT * FROM UserLog"
"2016-04-26 09:34:56","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",86,0,"Quit",""

"2016-04-26 09:34:55","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",86,0,"Connect","MJAPP_SoundU@localhost on MJAPP_SoundU2"

"2016-04-26 09:34:55","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",86,0,"Query","set names utf8"

"2016-04-26 09:34:55","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",86,0,"Query","SET GLOBAL time_zone='+8:00'"

"2016-04-26 09:34:55","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",86,0,"Query","SELECT * FROM UserLog"

"2016-04-26 09:34:56","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",86,0,"Quit",""

設定為長連接 PDO::ATTR_PERSISTENT => true

$PDO = new PDO( PDO_DSN, MYSQL_USERNAME, MYSQL_PASSWORD, array(
    PDO::ATTR_PERSISTENT => true
) );
$PDO->exec("set names utf8");

$sql = " SELECT * FROM UserLog  ";
$pre = $PDO->prepare($sql);
$pre->execute();

$PDO = new PDO( PDO_DSN, MYSQL_USERNAME, MYSQL_PASSWORD, array(

PDO::ATTR_PERSISTENT => true

) );

$PDO->exec("set names utf8");

$sql = " SELECT * FROM UserLog ";

$pre = $PDO->prepare($sql);

$pre->execute();

長連接，一次資料庫查詢結果

"2016-04-26 09:38:38","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",87,0,"Connect","MJAPP_SoundU@localhost on MJAPP_SoundU2"
"2016-04-26 09:38:38","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",87,0,"Query","set names utf8"
"2016-04-26 09:38:38","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",87,0,"Query","SELECT * FROM UserLog"

"2016-04-26 09:38:38","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",87,0,"Connect","MJAPP_SoundU@localhost on MJAPP_SoundU2"

"2016-04-26 09:38:38","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",87,0,"Query","set names utf8"

"2016-04-26 09:38:38","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",87,0,"Query","SELECT * FROM UserLog"

短連接，連續多查詢程式

$PDO = new PDO( PDO_DSN, MYSQL_USERNAME, MYSQL_PASSWORD, array(
    PDO::ATTR_PERSISTENT => false
) );
$PDO->exec("set names utf8");

$sql = " SELECT * FROM UserLog LIMIT 1 ";
$pre = $PDO->prepare($sql);
$pre->execute();
$sql = " SELECT * FROM UserLog LIMIT 1 ";
$pre = $PDO->prepare($sql);
$pre->execute();
$sql = " SELECT * FROM UserLog LIMIT 1 ";
$pre = $PDO->prepare($sql);
$pre->execute();

$PDO = new PDO( PDO_DSN, MYSQL_USERNAME, MYSQL_PASSWORD, array(

PDO::ATTR_PERSISTENT => false

) );

$PDO->exec("set names utf8");

$sql = " SELECT * FROM UserLog LIMIT 1 ";

$pre = $PDO->prepare($sql);

$pre->execute();

$sql = " SELECT * FROM UserLog LIMIT 1 ";

$pre = $PDO->prepare($sql);

$pre->execute();

$sql = " SELECT * FROM UserLog LIMIT 1 ";

$pre = $PDO->prepare($sql);

$pre->execute();

短連接，連續多查詢結果

"2016-04-26 10:57:16","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",123,0,"Connect","MJAPP_SoundU@localhost on MJAPP_SoundU2"
"2016-04-26 10:57:16","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",123,0,"Query","set names utf8"
"2016-04-26 10:57:16","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",123,0,"Query","SELECT * FROM UserLog LIMIT 1"
"2016-04-26 10:57:16","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",123,0,"Query","SELECT * FROM UserLog LIMIT 1"
"2016-04-26 10:57:16","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",123,0,"Query","SELECT * FROM UserLog LIMIT 1"
"2016-04-26 10:57:16","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",123,0,"Quit",""

"2016-04-26 10:57:16","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",123,0,"Connect","MJAPP_SoundU@localhost on MJAPP_SoundU2"

"2016-04-26 10:57:16","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",123,0,"Query","set names utf8"

"2016-04-26 10:57:16","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",123,0,"Query","SELECT * FROM UserLog LIMIT 1"

"2016-04-26 10:57:16","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",123,0,"Quit",""

長連接，連續多查詢程式

$PDO = new PDO( PDO_DSN, MYSQL_USERNAME, MYSQL_PASSWORD, array(
    PDO::ATTR_PERSISTENT => true
) );
$PDO->exec("set names utf8");

$sql = " SELECT * FROM UserLog LIMIT 1 ";
$pre = $PDO->prepare($sql);
$pre->execute();
$sql = " SELECT * FROM UserLog LIMIT 1 ";
$pre = $PDO->prepare($sql);
$pre->execute();
$sql = " SELECT * FROM UserLog LIMIT 1 ";
$pre = $PDO->prepare($sql);
$pre->execute();

$PDO = new PDO( PDO_DSN, MYSQL_USERNAME, MYSQL_PASSWORD, array(

PDO::ATTR_PERSISTENT => true

) );

$PDO->exec("set names utf8");

$sql = " SELECT * FROM UserLog LIMIT 1 ";

$pre = $PDO->prepare($sql);

$pre->execute();

$sql = " SELECT * FROM UserLog LIMIT 1 ";

$pre = $PDO->prepare($sql);

$pre->execute();

$sql = " SELECT * FROM UserLog LIMIT 1 ";

$pre = $PDO->prepare($sql);

$pre->execute();

長連接，連續多查詢結果

"2016-04-26 10:58:28","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",66,0,"Query","set names utf8"
"2016-04-26 10:58:28","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",66,0,"Query","SELECT * FROM UserLog LIMIT 1"
"2016-04-26 10:58:28","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",66,0,"Query","SELECT * FROM UserLog LIMIT 1"
"2016-04-26 10:58:28","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",66,0,"Query","SELECT * FROM UserLog LIMIT 1"

"2016-04-26 10:58:28","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",66,0,"Query","set names utf8"

"2016-04-26 10:58:28","MJAPP_SoundU[MJAPP_SoundU] @ localhost []",66,0,"Query","SELECT * FROM UserLog LIMIT 1"

—

MySQL SELECT REGEXP, PHP 刪除空白陣列

Posted on 2015-10-05 by hoyo

MySQL REGEXP

參考至： MySQL IN with LIKE

SELECT * 
FROM  `Time_BackMenu` 
WHERE URL REGEXP "Back/StaffBrowse|Back/JoinAccept|Back/SetCompany"

SELECT *

FROM `Time_BackMenu`

WHERE URL REGEXP "Back/StaffBrowse|Back/JoinAccept|Back/SetCompany"

—

PHP Array()

array_filter 移除陣列內空白
array_keys 取得陣列內所有的 Key
in_array 查詢該字串是否存在陣列內

—

MySQL 的雙向加密

Posted on 2014-05-21 by hoyo

參考至：MySQL加密函數保護Web網站敏感數據 – 王朝網路 – wangchao.net.cn

加密 SQL

SELECT HEX(ENCODE( 'HOYO', 'ABC' ));

1	SELECT HEX(ENCODE( 'HOYO', 'ABC' ));

解密 SQL

SELECT DECODE( UNHEX('92153DBB'), 'ABC' );

1	SELECT DECODE( UNHEX('92153DBB'), 'ABC' );

參數引數一為處理字串，引數二為金鑰。金鑰兩者必須相同方可解出正確字串。

—

MySQL 使用備忘

Posted on 2013-02-04 by hoyo

使用 .sql 匯入資料庫

How to import an SQL file using the command line in MySQL?

# mysql -u username -p database_name < file.sql

1	# mysql -u username -p database_name < file.sql

—

使用 phpMyAdmin 匯入有 foreign key 的資料表處理方案

匯入 fk 資料表時，就算所有關聯資料表一起匯入也會因為順序不一致而發生

Cannot add or update a child row: a foreign key constraint fails

1	Cannot add or update a child row: a foreign key constraint fails

這個錯誤

從網站上可以查詢到必須設定 SET FOREIGN_KEY_CHECKS=0; ，但是使用 SQL 指令執行後再使用 SELECT @@FOREIGN_KEY_CHECKS; 查看會發現還是 1 ，也就是沒有被關閉

解決的方法是修改匯入 .sql 檔案，在最開頭加上 SET FOREIGN_KEY_CHECKS=0; 以及結尾加上 SET FOREIGN_KEY_CHECKS=1;

類似像這樣

SET FOREIGN_KEY_CHECKS=0;

-- 
-- 原來的 SQL 指令
--

SET FOREIGN_KEY_CHECKS=1;

SET FOREIGN_KEY_CHECKS=0;

-- 原來的 SQL 指令

SET FOREIGN_KEY_CHECKS=1;

—

MySQL InnoDB 修復

How do I repair an InnoDB table?

—

同時使用 Group by 和 Order by 得到錯誤的結果

mysql “group by ”与”order by”的研究－－分类中最新的内容

SELECT Users_id, ipv4 
FROM 
(select id,users_id,ipv4 from UserLog order by id desc) as temp 
group by users_id 
order by id desc;

SELECT Users_id, ipv4

FROM

(select id,users_id,ipv4 from UserLog order by id desc) as temp

group by users_id

order by id desc;

在 UserLog 資料表紀錄了使用者 id 以及網路 ip ，這是一個很常用的應用，如果要取出某 id 最後登入的 ip 位址，在同時使用 Group by Order by 之下就會得到錯誤的資料。

要注意的是，並不是錯誤的語法而是得到的資料我們想像中的資料，如果你有認真查驗過的話。

JOIN 的場合

直接 JOIN 倒序排序的資料即可

select Users.id, ipv4 
from Users 
JOIN 
(select id,Users_id,ipv4 from UserLog order by id desc) `UserLog` 
ON 
Users.id=UserLog.Users_id

select Users.id, ipv4

from Users

JOIN

(select id,Users_id,ipv4 from UserLog order by id desc) `UserLog`

Users.id=UserLog.Users_id

MariaDB 5.5 的腦殘 bug – 子查詢 ORDER BY 無效

SELECT * FROM `UserLog` WHERE `Users_id` = 565 ORDER BY `UserLog`.`id` DESC

1	SELECT * FROM `UserLog` WHERE `Users_id` = 565 ORDER BY `UserLog`.`id` DESC

上面這樣可以得到以 id 的倒序排序，這個沒問題，如果改成子查訊會如何？

select * from 
(SELECT * FROM `UserLog` WHERE `Users_id` = 565 ORDER BY `UserLog`.`id` DESC) `a`

1 2	select * from (SELECT * FROM `UserLog` WHERE `Users_id` = 565 ORDER BY `UserLog`.`id` DESC) `a`

你就會得到一個沒有排過序的資料 …

為什麼會說這個是 bug ，因為一來 MySQL 5.1 沒這個問題，二來在 MariaDB 官網提供了一個黑科技解決這個問題

select * from 
(SELECT * FROM `UserLog` WHERE `Users_id` = 565 ORDER BY `UserLog`.`id` DESC 
LIMIT 18446744073709551615) `a`

select * from

(SELECT * FROM `UserLog` WHERE `Users_id` = 565 ORDER BY `UserLog`.`id` DESC

LIMIT 18446744073709551615) `a`

那一串 LIMIT 不是黑科技？什麼才叫做黑科技 …

—

#1093 You can’t specify target table for update in FROM clause

MySQL 更新或刪除如果使用子查詢，遇到子查詢也使用到更新、刪除資料表時就會發生這個錯誤。

比較簡單的處理方式是，在子查詢使用 AS 規避資料表名稱

SELECT * FROM AAA

1	SELECT * FROM AAA

改成

SELECT * FROM ( SELECT * FROM AAA) AS AliasAAA

1	SELECT * FROM ( SELECT * FROM AAA) AS AliasAAA

更新案例

UPDATE Booking SET ClassPoint =0 WHERE id = ( SELECT id
FROM (

SELECT id
FROM Booking
WHERE Classroom_id =  '123'
) AS B )

UPDATE Booking SET ClassPoint =0 WHERE id = ( SELECT id

FROM (

SELECT id

FROM Booking

WHERE Classroom_id = '123'

) AS B )

刪除案例

首先編輯挑選條件

SELECT Users.id 
FROM Users 
JOIN UserLog ON Users.id=UserLog.Users_id 
WHERE 
    UserIdentity_Code='Free'
AND 
    IPv4='211.20.170.215'
AND 
    DATE_FORMAT( DATE_SUB(NOW(), INTERVAL 7 DAY), '%Y%m%d') >= DATE_FORMAT( UserLog.Create_Time, '%Y%m%d') 
GROUP BY Users.id

SELECT Users.id

FROM Users

JOIN UserLog ON Users.id=UserLog.Users_id

WHERE

UserIdentity_Code='Free'

AND

IPv4='211.20.170.215'

AND

DATE_FORMAT( DATE_SUB(NOW(), INTERVAL 7 DAY), '%Y%m%d') >= DATE_FORMAT( UserLog.Create_Time, '%Y%m%d')

GROUP BY Users.id

將適合對象套用刪除，因為相同資料表也發生了 #1093 錯誤

DELETE FROM Users 
WHERE id 
IN ( 
SELECT AliasUsers.id 
FROM (
SELECT * FROM Users) AS AliasUsers 
JOIN UserLog ON AliasUsers.id=UserLog.Users_id 
WHERE 
    UserIdentity_Code='Free' 
AND 
    IPv4='211.20.170.215' 
AND 
    DATE_FORMAT( DATE_SUB(NOW(), INTERVAL 7 DAY), '%Y%m%d') >= DATE_FORMAT( UserLog.Create_Time, '%Y%m%d') 
GROUP BY AliasUsers.id
)

DELETE FROM Users

WHERE id

IN (

SELECT AliasUsers.id

FROM (

SELECT * FROM Users) AS AliasUsers

JOIN UserLog ON AliasUsers.id=UserLog.Users_id

WHERE

UserIdentity_Code='Free'

AND

IPv4='211.20.170.215'

AND

DATE_FORMAT( DATE_SUB(NOW(), INTERVAL 7 DAY), '%Y%m%d') >= DATE_FORMAT( UserLog.Create_Time, '%Y%m%d')

GROUP BY AliasUsers.id

)

—

建立跨資料庫外鍵

因為 phpMyAdmin 無法執行這個操作，只好自己手工輸入了…

ALTER TABLE `Albums`.`Supervisor` ADD FOREIGN KEY ( `Member_id` ) REFERENCES  `Member`.`Member` (
`id`
) ON DELETE CASCADE ON UPDATE CASCADE ;

ALTER TABLE `Albums`.`Supervisor` ADD FOREIGN KEY ( `Member_id` ) REFERENCES `Member`.`Member` (

`id`

) ON DELETE CASCADE ON UPDATE CASCADE ;

#1005 – Can’t create table
Percona-XtraDB, Supports transactions, row-level locking, and foreign keys

如果外鍵關聯動作設定為 SET NULL 就有可能會出現這個錯誤，改為其他動作試試

—

新增 FOREIGN KEY 發生 #1452 – Cannot add or update a child row: a foreign key constraint fails 錯誤

參考：

使用 Foreign Key 關鍵

主鍵必須為索引
外部鍵必須為 Primary Key
動作請設定為 CASCADE 讓關聯資料隨著刪除、修改一併更動
如果發生 #1452 錯誤，應該是資料表有對應錯誤的資料，必須要正確才能順利建立或修改

—

MySQL 的日期、時間切割計算

資料表有一個 time 欄位，型態為 timestamp

需求：顯示小時區間以及該小時區間的數值 Data 平均值，平均值取小數點 0 位

SELECT DATE_FORMAT(time, '%Y/%m/%d %H') D, FORMAT(AVG(`Data`),0) as T
FROM `Table`
GROUP BY MONTH(time),DAY(time),HOUR(time)

SELECT DATE_FORMAT(time, '%Y/%m/%d %H') D, FORMAT(AVG(`Data`),0) as T

FROM `Table`

GROUP BY MONTH(time),DAY(time),HOUR(time)

—

UNION 的 ORDER BY, LIMIT 及 offset

UNION 可將多條顯示相同欄位的 SQL 語句串連，建議將個別 SQL Command 使用 () 包住

( SELECT * FROM T1 LIMIT 0, 10 )
UNION
( SELECT * FROM T2 LIMIT 0, 10 )

( SELECT * FROM T1 LIMIT 0, 10 )

UNION

( SELECT * FROM T2 LIMIT 0, 10 )

UNION 的總 LIMIT 雖然可以套過下面語法實現，不過和想像中的分頁是不一樣的。

如果兩條 Query 都產生了 10 條結果，那最後的 LIMIT 就只會顯示前 10 筆

( SELECT * FROM T1 LIMIT 0, 10 )
UNION
( SELECT * FROM T2 LIMIT 0, 10 )
LIMIT 0, 10

( SELECT * FROM T1 LIMIT 0, 10 )

UNION

( SELECT * FROM T2 LIMIT 0, 10 )

LIMIT 0, 10

也可以對最後結果進行排序或其他判斷

( SELECT * FROM T1 LIMIT 0, 10 )
UNION
( SELECT * FROM T2 LIMIT 0, 10 )
ORDER BY Column DESC

( SELECT * FROM T1 LIMIT 0, 10 )

UNION

( SELECT * FROM T2 LIMIT 0, 10 )

ORDER BY Column DESC

—

子查詢

>=ANY 大于等于子查询中的某个值
<=ANY 小于等于子查询中的某个值
=ANY 等于子查询中的某个值
!=ANY或<>ANY 不等于子查询中的某个值
>ALL 大于子查询中的所有值
>=ALL 大于等于子查询中的所有值
<=ALL 小于等于子查询中的所有值
=ALL 等于子查询中的所有值
!=ALL或<>ALL 不等于子查询中的所有值

應用：留言回覆

SELECT * FROM 訊息 WHERE sid=ANY ( SELECT 回覆sid FROM 訊息 WHERE 回覆sid<>'' ) ORDER BY sid DESC LIMIT 0, 1

1	SELECT * FROM 訊息 WHERE sid=ANY ( SELECT 回覆sid FROM 訊息 WHERE 回覆sid<>'' ) ORDER BY sid DESC LIMIT 0, 1

—

從現有資料複製 INSERT INTO SELECT

INSERT INTO `Media_File` ( create_date,source_filename,create_user,media_filename,來源媒體資訊,產品連結)
SELECT '20131218100600', source_filename,create_user,media_filename,來源媒體資訊,'166'
FROM `Media_File` WHERE sid=110724

INSERT INTO `Media_File` ( create_date,source_filename,create_user,media_filename,來源媒體資訊,產品連結)

SELECT '20131218100600', source_filename,create_user,media_filename,來源媒體資訊,'166'

FROM `Media_File` WHERE sid=110724

—

MySQL 插入重複唯一值錯誤解決

INSERT IGNORE INTO `table_name` (`email`, `phone`, `user_id`) VALUES ('test9@163.com', '99999', '9999');

1	INSERT IGNORE INTO `table_name` (`email`, `phone`, `user_id`) VALUES ('test9@163.com', '99999', '9999');

—

#1194 – Table ‘tablename’ is marked as crashed and should be repaired

MyISAM 資料表毀損修復

參考：★用myisamchk修復損毀資料表 | ★羽毛貓のノート★

/etc/rc.d/init.d/mysqld stop 停止資料庫
myisamchk -f -r *.MYI
/etc/rc.d/init.d/mysqld start

主要的關鍵是：必須先停止資料庫，才可以正常修復資料表

—

開啟 log

開啟 general-log 功能可以紀錄 MySQL 所有執行的 SQL 指令，

show variables like 'log';

1	show variables like 'log';

編輯 my.cnf

[mysqld]
general-log = 1 # 打開 General Log
log_output=TABLE # 將 Log 儲存在 mysql 資料庫 內的 general_log 資料表內

[mysqld]

general-log = 1 # 打開 General Log

log_output=TABLE # 將 Log 儲存在 mysql 資料庫內的 general_log 資料表內

使用 SQL 指令修改 Log 狀態

SET GLOBAL general_log = 'ON';

1	SET GLOBAL general_log = 'ON';

—

mysqldump UTF-8 資料庫卻不是 UTF-8 編碼檔案？

如果原資料庫或資料表的排序規則是 utf8_general_ci 的話，那修改成 utf8_unicode_ci 之後再測試看看。

—

關閉 log-bin

mysql-bin.000001文件的來源及處理方法 – PHP+MySQL – 程式設計 – 頂客論壇 – 台灣forum,Taiwan論壇bbs

執行以下 SQL 指令，清除檔案

reset master;

1	reset master;

註解 my.cnf 內的 log-bin ，重新啟動 MySQL Server

—

修改語系為 UTF8

Change MySQL default character set to UTF-8 in my.cnf?

編輯 /etc/my.cnf 加入以下設定

[mysqld]
init-connect = 'SET NAMES utf8'
character-set-server = utf8

[client]
default-character-set=utf8

[mysqld]

init-connect = 'SET NAMES utf8'

character-set-server = utf8

[client]

default-character-set=utf8

init-connect=’SET NAMES utf8′
character_set_system | utf8
character-set-server = utf8
character_set_server | utf8
character_set_database | utf8
default-character-set=utf8
character_set_client | utf8
character_set_connection | utf8
character_set_results | utf8

mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

mysql> show variables like 'char%';

+--------------------------+----------------------------+

| Variable_name | Value |

+--------------------------+----------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

使用 PHP 程式設定

define( "PDO_DSN", "mysql:dbname=". MYSQL_DBNAME .";host=". MYSQL_HOST.";charset=utf8" );

$options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8');
$PDO = new PDO(PDO_DSN, MYSQL_USERNAME, MYSQL_PASSWORD, $options);
$PDO->exec("set character_set_server=utf8;");

define( "PDO_DSN", "mysql:dbname=". MYSQL_DBNAME .";host=". MYSQL_HOST.";charset=utf8" );

$options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8');

$PDO = new PDO(PDO_DSN, MYSQL_USERNAME, MYSQL_PASSWORD, $options);

$PDO->exec("set character_set_server=utf8;");

php://input 的陷阱

對於 php://input 的編碼只能藉由 my.cnf 的設定，php 尚未取得設定方法

—

顯示 MySQL 組態設定

show variables like 'collation%';

1	show variables like 'collation%';

— Get the current value that could be already modified by SET sql_mode statement
— executed in the current session
SELECT @@sql_mode;

— Get the global value, not affected by SET sql_mode
SELECT @@GLOBAL.sql_mode;

—

將 SQL mode 移除預設嚴格模式

修改 my.ini

[mysqld]
sql_mode=

1 2	[mysqld] sql_mode=

或使用 SQL 指令

SET GLOBAL sql_mode = '';

1	SET GLOBAL sql_mode = '';

—

hoyo 學習紀錄

PHP 網頁應用 IOT 物聯網自動化

Category Archives: MySQL

MySQL 快速匯入 CSV 檔案 – 使用 LOAD DATA

實際範例

效能

ERROR 1045 (28000): Access denied for user ‘XXXXX’@’%’ (using password: YES)

MySQL Partitioning 分區

限制

以時間作為分區依據

分區區分到年、月

InnoDB 如何分區區分檔案

使用 HASH

分區刪除合併檔案變化

查詢時是否有效套用到分區？

PDO 長連接 PDO::ATTR_PERSISTENT 測試及了解

PHP PDO MySQL 長連結設定及影響

MySQL SELECT REGEXP, PHP 刪除空白陣列

MySQL REGEXP

PHP Array()

MySQL 的雙向加密

MySQL 使用備忘

使用 .sql 匯入資料庫

使用 phpMyAdmin 匯入有 foreign key 的資料表處理方案

MySQL InnoDB 修復

同時使用 Group by 和 Order by 得到錯誤的結果

#1093 You can’t specify target table for update in FROM clause

建立跨資料庫外鍵

新增 FOREIGN KEY 發生 #1452 – Cannot add or update a child row: a foreign key constraint fails 錯誤

MySQL 的日期、時間切割計算

UNION 的 ORDER BY, LIMIT 及 offset

子查詢

從現有資料複製 INSERT INTO SELECT

MySQL 插入重複唯一值錯誤解決

開啟 log

mysqldump UTF-8 資料庫卻不是 UTF-8 編碼檔案？

關閉 log-bin

修改語系為 UTF8

將 SQL mode 移除預設嚴格模式