Incorrect datetime value: '' for column 'updatetime' at row 1

Incorrect datetime value: '' for column 'updatetime' at row 1


mysql  run

mysql> set session sql_mode='';

mysql> set global sql_mode=''; 

CentOS 升級 php4 mysql4

yum -y --enablerepo=centosplus --exclude=php-domxml install php*

yum -y --enablerepo=centosplus --exclude=mysql-domxml install mysql*

SQL資料內容置換語法

update 資料表 set 資料欄位 = replace( 資料欄位 , '舊字串', '新字串');

.

MySQL 使用 UTF-8 編碼時的中文排序

當資料庫編碼是使用 UTF-8 時，若有欄位是中文，且要依此欄位進行排序時，

MySQL將會依 UTF-8 的編碼來排序，而不會依我們所預期的 BIG5 來排序。

若要解決此問題，只要在排序時，將排序的欄位轉為 BIG5 就可以依筆畫排序了。

使用方式就是 CONVERT( 欄位名稱 using big5) 。

例如：

Select factory From wrongnote Order By CONVERT( factory using big5 )

.

MySQL 及 MsSQL 隨機選出資料

MySQL

select * from MyTable order by RAND()

MsSQL

select * from MyTable order by NEWID()

Access

select * from MyTable order by RND(數值型的列名稱)

MySQL Big5 轉 utf8

◎ PHP 與 MySQL 建立連線：
所以為了解決亂碼問題，在PHP 與 MySQL 建立連線時，於 query 之前先執行：

mysql_query("SET CHARACTER_SET_CLIENT=utf8");
mysql_query("SET CHARACTER_SET_CONNECTION=utf8");
mysql_query("SET CHARACTER_SET_RESULTS=utf8");

如上三列編碼都一樣時，可直接執行下列

mysql_query("SET NAMES utf8");

◎ MySQL資料庫big5轉utf8：

Step1：首先把資料dump出來

由於先前資料是存成latin1，所以dump出來後要用iconf或piconf把他轉成UTF-8，記得dump出來時要加上-default-character-set=latin1

#mysqldump -u 帳號 -p --default-character-set=latin1 資料庫 >output.sql

Step2：big5轉成utf8

如果原先是Big5的資料， dump出來後就直接把Big5轉成UTF-8即可

#piconv -f big5 -t utf8 output.sql >utf8.sql

如果原先是UTF-8的資料，dump出來後要先轉成Big5後，再把他轉成UTF-8，因為原先dump出來的是偽裝成UTF-8的lantin1不是真正的UTF-8

#mysqldump -u 帳號 -p -default-character-set=latin1 資料庫 >output.sql
#piconv -f utf8 -t big5 output.sql > big5.sql
#piconv -f big5 -t utf8 big5.sql >utf8.sql

Step3：修改sql檔

1、修改轉成的sql檔中所產生的‘’\”及換行的問題(有時換行時會成‘\n’)

2、在dump出來的sql檔最前面加上：
SET NAMES utf8;
SET CHARACTER_SET_CLIENT=utf8;
SET CHARACTER_SET_RESULTS=utf8;

:1,$s/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/g
:1,$s/latin1/utf8/gc
:1,$s/許\\/許/g
:1,$s/功\\/功/g
:1,$s/蓋\\/蓋/g
:1,$s/餐\\/餐/g
:1,$s/愧\\/愧/g
:1,$s/擺\\/擺/g
:1,$s/穀\\/穀/g
:1,$s/淚\\/淚/g
:1,$s/豹\\/豹/g
:1,$s/珮\\/珮/g
:1,$s/俞\\/俞/g


再來把每個資料表後面的TYPE=MyISAM是否有DEFAULT CHARSET=latin1;
若沒有請改為ENGINE=MyISAM DEFAULT CHARSET=utf8;

Step4：建立新的資料庫

方法1. SQL語法
CREATE DATABASE `資料庫` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

方法2. 用phpMyAdmin建立資料庫的，建立時”校對”要選utf8_general_ci

步驟五：import資料

方法1. SQL語法

#mysql -u 帳號 -p 資料庫 < utf8.sql

方法2. 直接用phpMyAdmin把他import進去了，import時記得文字編碼檔案要選utf8即可。不過使用phpMyAdmin import 有檔案大小的限制，可先把sql檔分成幾個檔案。

◎ 最後記得檢查看MySQL的my.cnf設定檔內要加入以下設定
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
default-character-set=utf8
default-collation=utf8_general_ci

替換 MySQL 資料庫資料

資料庫　　　DataBaseName
資料表　　　TableName
欄位　　　　FieldName
變更前字串　SourceString
變更後字串　ChangeString

update `DataBaseName`.`TableName` set `FieldName` = REPLACE( `FieldName`, 'SourceString', 'ChangeString')

MySQL 匯出匯入指令

[匯出]
mysqldump -u userid -e -p db_Name > xxxxx.sql


[有條件匯出]
mysqldump -u帳號 -p密碼 -h主機 資料庫 資料表 -w "sql條件" > 出輸路徑及檔案
例:
mysqldump -uroot -p123456 -hlocalhost -e AREA_UTF8 city -w "c_id<10 " > /home/web/a.txt

--no-create-info，-t
只導出資料，而不添加 CREATE TABLE 語句；如果導出格式為SQL語句，則只有insert into部分。
--no-data，-d
不導出任何資料，只導出資料庫結構
--quick，-q
在導出大量資料很有用，強制從 MySQL Server 查詢取得記錄直接輸出，而不是取得所有記錄後存在記憶體中。


[匯入]
mysql -u userid -p [-h localhost] db_Name < xxxxx.sql


[匯出後立即匯入到另一台主機]
mysqldump -u [SOURCE_userid] -h [SOURCE_IP] -e -q --password=[SOURCE_PASSWORD] SOURCE_db_Name | mysql -u [DISTANCE_userid] --password=[DISTANCE_PASSWORD] -h [DISTANCE_IP] DISTANCE_db_Name

PHP 上傳檔案並寫入MySQL 出現 MySQL server has gone away

如果你向伺服器發送不正確或太大的查詢
你可能得到[MySQL server has gone away]錯誤。

如果mysqld得到一個太大或不正常的packet，它認為客戶出錯了並關閉連接。
所以你需要較大的查詢(例如，如果你正在處理較大的BLOB列)時，

你可以使用 -O max_allowed_packet=#

重新啟動mysqld以增加查詢限制。

需分配多餘的記憶體，這樣mysqld只有在你使用較大差詢時或mysqld必須返回較大的結果行時，才能使用更多的記憶體！


以下為修改方式

打開my.ini 找到[mysqld]這一行，下方添加

[mysqld]
#擴大緩衝區
max_allowed_packet=16M

SAVE後，重啟mysql

常用的MySQL指令

【啟動與停止】
啟動
shell> mysql.server start

停止
shell> mysql.server stop

mysql.server這個script在MySQL安裝目錄(在FreeBSD是/usr/local)的share/mysql下

【3.1與Server的連線與中斷】
連線
shell> mysql -h host -u user -p
Enter password: ********

中斷
有三種方式
mysql> QUIT
mysql> quit;
按Control-D


【3.2輸入查詢】
mysql> SELECT VERSION(), CURRENT_DATE;
+--------------+--------------+
| version() | CURRENT_DATE |
+--------------+--------------+
| 3.22.20a-log | 1999-03-19 |
+--------------+--------------+
row in set (0.01 sec)
mysql>
指令後要加一個分號(;)表示結束（有某些例外，如前述的QUIT）
指令關鍵字不分大小寫

另類查詢，可將mysql當計算器
mysql> SELECT SIN(PI()/4), (4+1)*5;
+-------------+---------+
| SIN(PI()/4) | (4+1)*5 |
+-------------+---------+
| 0.707107 | 25 |
+-------------+---------+

也可以在一行中輸入多項指令
mysql> SELECT VERSION(); SELECT NOW();

也可以將一項指令分成多行輸入
mysql> SELECT

-> USER()

-> ,

-> CURRENT_DATE;

如果要取消之前的輸入可鍵入\c
mysql> SELECT

-> USER()

-> \c
mysql>

提示符號的意義：
提示符號 意義
mysql> 可下新指令
-> 等待下一行，多項指令時
,> 等待下一行，收集以'開頭的字串
"> 等待下一行，收集以"開頭的字串

【3.3建立與使用資料庫】
顯示目前有幾個資料庫
mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql |
| test |
| tmp |
+----------+

使用test資料庫
mysql> USE test
Database changed
注意USE一QUIT一樣不需在後加分號，USE敘述只能寫在一行中

在test中的任何東西，任何人都可以將其刪除，所以請管理員我們設好一個資料庫的權限。假設這個資料庫叫menagerie，管理員必須執行這樣的指令：
mysql> GRANT ALL ON menagerie.* TO your_mysql_name;

【3.3.1建立與選擇資料庫】
建立資料庫
mysql> CREATE DATABASE menagerie;

使用menagerie，使它成為目前的資料庫
mysql> USE menagerie
Database changed
建立資料庫的步驟只要做一次，但是每次開始一個mysql session時都要做如上的使用動作。或是當使用mysql時就選好資料庫：
shell> mysql -h host -u user -p menagerie
Enter password: ********
注意在這裡menagerie不是密碼，如果要將密碼放在-p後面，要像這樣：-pmypassword。但不建議這麼做。

【3.3.2建立表格】
顯示表格
mysql> SHOW TABLES;
Empty set (0.00 sec)
剛建好資料庫時，裡面是空的

建立表格
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
-> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

顯示表格結構
mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+

【3.3.3載入資料到表格中】
使用LOAD DATA與INSERT敘述
假設pet的資料如下：（這裡YYYY-MM-DD的日期格式可能與你所用的不同）
name owner species sex birth death
Fluffy Harold cat f 1993-02-04
Claws Gwen cat m 1994-03-17
Buffy Harold dog f 1989-05-13
Fang Benny dog m 1990-08-27
Bowser Diane dog m 1998-08-31 1995-07-29
Chirpy Gwen bird f 1998-09-11
Whistler Gwen bird 1997-12-09
Slim Benny snake m 1996-04-29
只為是從空白資料庫開始，所以最簡單的方法是將這些做成文字檔，然後用一行敘述將這些載入資料庫中。
可以建立一個pet.txt，其中每行一筆記錄，用tab隔開其中的值。不明的值可用NULL，在文字檔中，以\N表示。以Whistler這筆為例，會像這樣：
Whistler Gwen bird \N 1997-12-09 \N
用這個命令將pet.txt載入pet表格中：
mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;
可以在LOAD DATA敘述中指定欄的分隔符號與行的結尾符號，預設是tab與linefeed

一次加入一筆資料，使用INSERT
mysql> INSERT INTO pet
-> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

【3.3.4從表格取出資訊】
SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy

【3.3.4.1選擇所有資料】
mysql> SELECT * FROM pet;
這時發現Bowser的birth是1989而非1988，有幾種方式來改正：
．編輯pet.txt，修正這個錯誤，清掉這表格，重新載入：
mysql> SET AUTOCOMMIT=1; # Used for quick re-create of the table
mysql> DELETE FROM pet;
mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;
．只修正有錯誤的記錄：
mysql> UPDATE pet SET birth = "1989-08-31" WHERE name = "Bowser";

【3.3.4.2選擇特定列】
mysql> SELECT * FROM pet WHERE name = "Bowser";
mysql> SELECT * FROM pet WHERE birth >= "1998-1-1";
mysql> SELECT * FROM pet WHERE species = "dog" AND sex = "f";
mysql> SELECT * FROM pet WHERE species = "snake" OR species = "bird";
mysql> SELECT * FROM pet WHERE (species = "cat" AND sex = "m")
-> OR (species = "dog" AND sex = "f");

【3.3.4.3選擇特定欄】
mysql> SELECT name, birth FROM pet;
mysql> SELECT owner FROM pet;
mysql> SELECT DISTINCT owner FROM pet;
mysql> SELECT name, species, birth FROM pet
-> WHERE species = "dog" OR species = "cat";

【3.3.4.4輸出時依列排序】
mysql> SELECT name, birth FROM pet ORDER BY birth;
mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC;

其它SQL指令
DROP

ALTER

INSERT INTO

DELETE

UPDATE

【dump資料表結構與裡面的資料】
mysqldump -u 使用者名稱 -p 密碼 資料庫 [資料表1 [資料表2...]]

備份home資料庫到home20020627.sql檔案
mysqldump -u root -p home > home20020627.sql

備份所有的MySQL資料庫的到backup-file.sql
# mysqldump -p -A > backup-file.sql

【自dump讀回資料】
# mysql -u root -p < backup-file.sql

【更改使用者密碼】
MySQL的預設管理員username是root，預設無密碼
方法一：
shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password')
WHERE user='root';
mysql> FLUSH PRIVILEGES;

★方法二：
You can, in MySQL Version 3.22 and above, use the SET PASSWORD statement:
shell> mysql -u root mysql
mysql> SET PASSWORD FOR root=PASSWORD('new_password');

方法三：
Another way to set the password is by using the mysqladmin command:
shell> mysqladmin -u root password new_password

Only users with write/update access to the mysql database can change the password for others users. All normal users (not anonymous ones) can only change their own password with either of the above commands or with SET PASSWORD=PASSWORD('new password').

【新增使用者】
可使用GRANT指令，同時新增使用者與給予權限的工作

GRANT and REVOKE語法
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY 'password']
[, user_name [IDENTIFIED BY 'password'] ...]
[REQUIRE
[{SSL| X509}]
[CIPHER cipher [AND]]
[ISSUER issuer [AND]]
[SUBJECT subject]]
[WITH GRANT OPTION]

REVOKE，取消權限
REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
FROM user_name [, user_name ...]

For the GRANT and REVOKE statements, priv_type may be specied as any of the following:
★ALL PRIVILEGES FILE RELOAD
★ALTER INDEX ★SELECT
★CREATE ★INSERT SHUTDOWN
★DELETE PROCESS ★UPDATE
★DROP REFERENCES USAGE

The WITH GRANT OPTION clause gives the user the ability to give to other users any privileges the user has at the specied privilege level.

新增使用者的例子：
shell> mysql --user=root mysql
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@localhost
IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON *.* TO admin@localhost;
mysql> GRANT USAGE ON *.* TO dummy@localhost;

【刪除使用者】
要用delete指令
mysql> USE mysql
mysql> delete from user where user = 'the_username_to_be_deleted';

【設定使用權限】
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON bankaccount.*
TO custom@localhost
IDENTIFIED BY 'stupid';

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON expenses.*
TO custom@whitehouse.gov
IDENTIFIED BY 'stupid';

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON customer.*
TO custom@'%'
IDENTIFIED BY 'stupid';


出處：小紅帽技術論壇