MySQL 使用備忘

SQLSTATE[HY000]: General error

PDO error: “ SQLSTATE[HY000]: General error ” When updating database

在 UPDATE, DELETE 後接 ->fetch() 之類的就會發生這個錯誤

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

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

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 = '';

3,551 total views, 1 views today

SQLSTATE[HY000]: General error

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 插入重複唯一值錯誤解決

#1194 - Table 'tablename' is marked as crashed and should be repaired

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

關閉 log-bin

修改語系為 UTF8

將 SQL mode 移除預設嚴格模式

Related Post

發佈留言

MySQL 使用備忘

SQLSTATE[HY000]: General error

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 插入重複唯一值錯誤解決

#1194 - Table 'tablename' is marked as crashed and should be repaired

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

關閉 log-bin

修改語系為 UTF8

將 SQL mode 移除預設嚴格模式

Related Post

MySQL - 錯誤代碼及解決思路

MySQL 5.7 - 忘記 root 密碼

MySQL - 排序

發佈留言