很多時候我們在下SQL語言時出錯卻一直找不出原因, 很多時候是因為在MYSQL內的COLUMN使用了reserved word 而造成的. 以下為MYSQL 的reserved word, 如果你在命名table.column時就要儘可能避開使用這些保留變數了.

ACCESSIBLE 	ADD 	ALL
ALTER 	ANALYZE 	AND
AS 	ASC 	ASENSITIVE
BEFORE 	BETWEEN 	BIGINT
BINARY 	BLOB 	BOTH
BY 	CALL 	CASCADE
CASE 	CHANGE 	CHAR
CHARACTER 	CHECK 	COLLATE
COLUMN 	CONDITION 	CONSTRAINT
CONTINUE 	CONVERT 	CREATE
CROSS 	CURRENT_DATE 	CURRENT_TIME
CURRENT_TIMESTAMP 	CURRENT_USER 	CURSOR
DATABASE 	DATABASES 	DAY_HOUR
DAY_MICROSECOND 	DAY_MINUTE 	DAY_SECOND
DEC 	DECIMAL 	DECLARE
DEFAULT 	DELAYED 	DELETE
DESC 	DESCRIBE 	DETERMINISTIC
DISTINCT 	DISTINCTROW 	DIV
DOUBLE 	DROP 	DUAL
EACH 	ELSE 	ELSEIF
ENCLOSED 	ESCAPED 	EXISTS
EXIT 	EXPLAIN 	FALSE
FETCH 	FLOAT 	FLOAT4
FLOAT8 	FOR 	FORCE
FOREIGN 	FROM 	FULLTEXT
GRANT 	GROUP 	HAVING
HIGH_PRIORITY 	HOUR_MICROSECOND 	HOUR_MINUTE
HOUR_SECOND 	IF 	IGNORE
IN 	INDEX 	INFILE
INNER 	INOUT 	INSENSITIVE
INSERT 	INT 	INT1
INT2 	INT3 	INT4
INT8 	INTEGER 	INTERVAL
INTO 	IS 	ITERATE
JOIN 	KEY 	KEYS
KILL 	LEADING 	LEAVE
LEFT 	LIKE 	LIMIT
LINEAR 	LINES 	LOAD
LOCALTIME 	LOCALTIMESTAMP 	LOCK
LONG 	LONGBLOB 	LONGTEXT
LOOP 	LOW_PRIORITY 	MASTER_SSL_VERIFY_SERVER_CERT
MATCH 	MEDIUMBLOB 	MEDIUMINT
MEDIUMTEXT 	MIDDLEINT 	MINUTE_MICROSECOND
MINUTE_SECOND 	MOD 	MODIFIES
NATURAL 	NOT 	NO_WRITE_TO_BINLOG
NULL 	NUMERIC 	ON
OPTIMIZE 	OPTION 	OPTIONALLY
OR 	ORDER 	OUT
OUTER 	OUTFILE 	PRECISION
PRIMARY 	PROCEDURE 	PURGE
RANGE 	READ 	READS
READ_WRITE 	REAL 	REFERENCES
REGEXP 	RELEASE 	RENAME
REPEAT 	REPLACE 	REQUIRE
RESTRICT 	RETURN 	REVOKE
RIGHT 	RLIKE 	SCHEMA
SCHEMAS 	SECOND_MICROSECOND 	SELECT
SENSITIVE 	SEPARATOR 	SET
SHOW 	SMALLINT 	SPATIAL
SPECIFIC 	SQL 	SQLEXCEPTION
SQLSTATE 	SQLWARNING 	SQL_BIG_RESULT
SQL_CALC_FOUND_ROWS 	SQL_SMALL_RESULT 	SSL
STARTING 	STRAIGHT_JOIN 	TABLE
TERMINATED 	THEN 	TINYBLOB
TINYINT 	TINYTEXT 	TO
TRAILING 	TRIGGER 	TRUE
UNDO 	UNION 	UNIQUE
UNLOCK 	UNSIGNED 	UPDATE
USAGE 	USE 	USING
UTC_DATE 	UTC_TIME 	UTC_TIMESTAMP
VALUES 	VARBINARY 	VARCHAR
VARCHARACTER 	VARYING 	WHEN
WHERE 	WHILE 	WITH
WRITE 	XOR 	YEAR_MONTH
ZEROFILL

The following are new reserved words in MySQL 5.1:

ACCESSIBLE 	LINEAR 	MASTER_SSL_VERIFY_SERVER_CERT
RANGE 	READ_ONLY 	READ_WRITE

參考資料: http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html

Some of you might be using the following directive to do Random Picking from your MYSQL database, which is mysql_query("SELECT * FROM table ORDER BY RAND() LIMIT 10″);

However, It is not a good way if your website is in a high-flow-situation, Using such directive might causing a big loading in your hosting machine, here’s the alternative way to replace such directive, (special thanks to Nicky Soo for providing this method),

The code should be look like this:

mysql_query(" SELECT *
FROM news AS r1 JOIN
(SELECT (RAND() *
(SELECT MAX(newsid) FROM news)) AS newsid) As r2
WHERE `display` =1
AND r1.newsid >= r2.newsid
AND r1.counter >50
LIMIT 0 , 10  ")

Cheers!

有時候我們用中文存取MYSQL資料庫資料的時候會出現亂碼,
又也許你在PHPMYADMIN看到的時候是明碼, 但用echo抓出來後卻是" ??? " 的亂碼.
又或是在儲存的時候就已經是亂碼了, 要怎麼解決呢?

可以試試看以下的方法:

mysql_query("SET NAMEs 'utf8' ");
$sql = mysql_query("SELECT * FROM wp_posts where ID = 2");

不管INSERT 或是SELECT出來, 都在mysql_query前加入

 "SET NAMES 'utf8' " 

就可以確保資料庫存取時是以UTF-8的編碼儲存, 那在做資料轉換的過程應該就可以把編碼問題解決了~

個人測試問題發生的背景如下:
環境: Windows Server 2003
先灌MYSQL 5.1 =>OK
再灌PHP4 =>OK
灌PHPMYADMIN =>設定Secret_blowfish OK=>跑的時候出現

Client does not support authentication protocol requested
by server; consider upgrading MySQL client

MYSQL官方的解決方式如下:

MySQL 5.0 uses an authentication protocol based on a password hashing algorithm that is incompatible with that used by older (pre-4.1) clients. If you upgrade the server from 4.0, attempts to connect to it with an older client may fail with the following message:

shell> mysql Client does not support authentication protocol requested by server; consider upgrading MySQL client

To solve this problem, you should use one of the following approaches:

  • Upgrade all client programs to use a 4.1.1 or newer client library.
  • When connecting to the server with a pre-4.1 client program, use an account that still has a pre-4.1-style password.
  • Reset the password to pre-4.1 style for each user that needs to use a pre-4.1 client program. This can be done using the SET PASSWORD statement and the OLD_PASSWORD() function:
    mysql> SET PASSWORD FOR
    -> 'some_user'@'some_host' = OLD_PASSWORD('newpwd');

    Alternatively, use UPDATE and FLUSH PRIVILEGES:

    mysql> UPDATE mysql.user SET Password = OLD_PASSWORD('newpwd')
    -> WHERE Host = 'some_host' AND User = 'some_user';
    mysql> FLUSH PRIVILEGES;

    Substitute the password you want to use for “newpwd” in the preceding examples. MySQL cannot tell you what the original password was, so you’ll need to pick a new one.

  • Tell the server to use the older password hashing algorithm:
    1. Start mysqld with the --old-passwords option.
    2. Assign an old-format password to each account that has had its password updated to the longer 4.1 format. You can identify these accounts with the following query:
      mysql> SELECT Host, User, Password FROM mysql.user
      -> WHERE LENGTH(Password) > 16;

      For each account record displayed by the query, use the Host and User values and assign a password using the OLD_PASSWORD() function and either SET PASSWORD or UPDATE, as described earlier.

Note

In older versions of PHP, the mysql extension does not support the authentication protocol in MySQL 4.1.1 and higher. This is true regardless of the PHP version being used. If you wish to use the mysql extension with MySQL 4.1 or newer, you may need to follow one of the options discussed above for configuring MySQL to work with old clients. The mysqli extension (stands for "MySQL, Improved"; added in PHP 5) is compatible with the improved password hashing employed in MySQL 4.1 and higher, and no special configuration of MySQL need be done to use this MySQL client library. For more information about the mysqli extension, see http://php.net/mysqli.

It may also be possible to compile the older mysql extension against the new MySQL client library. This is beyond the scope of this Manual; consult the PHP documentation for more information. You also be able to obtain assistance with these issues in our MySQL with PHP forum.

For additional background on password hashing and authentication, see Section 5.4.9, “Password Hashing as of MySQL 4.1”.

後來試了之後就可以RUN了, 如果各位也遇到這個情況不妨試試.

主要是針對不同的帳戶, 可能是版本的問題, 只要設定OLD_PASSWORD, 再FLUSH一次MYSQL應該就沒問題了.

原文可參考: http://dev.mysql.com/doc/refman/5.0/en/old-client.html

當我們要把一個MYSQL的資料庫輸出,
搬移到另一臺機器的MYSQL時,
如果是全英文的資料庫還好, 最怕的是遇到中文字編碼的問題.
如果欄位是VARCHAR的dataType, 搬移後用網頁顯示時會是" ??? "..
相信是因為big5跟UTF-8之間轉換所造成的問題.
我的解決方式是把相關的資料格式從VARCHAR 變成 BLOB.
中文字就能正常顯示了.

這樣做的缺點是資料格式若是BLOB則會減低效率,
而且用PHP函式對中文字作字串處理時會有問題.

僅供參考囉~

不知道各位使用PHPMYADMIN時, 會不會覺得BROWSER端控制MYSQL資料庫有時候會有點慢,
尤其是當遠端SERVER在美國時, 每一次POSTBACK都要等上幾秒鐘, 這樣做其實相當沒有效率.
要怎麼提高效率呢?

我想到了兩種方法:
1. 自己設計WIN FORM, 使用GRIDVIEW存取資料庫的資料:
搭配VISUAL STUDIO 的MYSQL ADO.NET CONNECTOR, 你可以自己設計WIN FORM, 不只是專屬於你個人的WINFORM, 也可以傳給你的管理者. 使用離線資料庫的處理方式. 在GRIDVIEW內, 新增,刪除,修改都可以直接REAL TIME進行, 當確定更新的時候, 才傳送回去遠端的資料庫. 是不是大大節省了在PHPMYADMIN 按來按去等待的時間呢?

2. MYSQL 網站提供的QUERY BROWSER.
http://www.mysql.com 網站上有提供免費的GUI控制程式, 如果懶惰設計WIN FORM, 就可以直接下載來用. 也是相當不錯的選擇喔. 直接用別人寫好的APP, 可以省下大量的時間, 但有可能要花心思來學習怎麼使用了.

(以上兩種方法的使用前提是你的MYSQL帳號類型是允許遠端存取(%), 而不是localhost而已喔)

如果各位跟我一樣, 有從PHP平臺搬移到.NET的經驗, 一定會卡在資料庫的移轉問題.
我們知道Microsof SQL SERVER 很好用, 而且號稱跟.NET平臺結合得天衣無縫,
但是如果我以前的資料都存放在MYSQL, 那該怎麼辦?

方法其實有兩種,

方法一(如果您堅持要搬資料庫)
可以先使用PHPMYADMIN來做匯出EXCEL或CSV的動作,  然後再通過.NET的程式把資料送入,
這個方式需要在MSSQL重新建立資料表, 然後再把資料批次送入, 這樣做就可以把MYSQL的資料完全移轉到MSSQL, 但這樣做既費時, 又要寫程式, 而且也難保不會有資料遺失.

方法二
MYSQL 其實已經幫您想好了解決方案, 在http://www.mysql.com 的官網中其實已經提供了免費的NAMESPACE, .dll, 讓您直接安裝在VISUAL STUDIO(VS)上, 從此你就可以用VS 來編輯MYSQL資料庫, 他的ConnectionString, DataAdapter, DataCommand等宣告方式跟MSSQL是大同小異的, 因此您可以輕輕鬆鬆存取MYSQL的資料, 又不必擔心會有資料遺失的問題.

下載的原件名稱為: CONNECTOR ADO.NET 5.1
網址: http://dev.mysql.com/downloads/connector/net/5.1.html
安裝方法: 直接打開 msi檔就會自動安裝到VISUAL STUDIO的NAMESPACE FOLDER裡

附件還包括了MYSQL CLASSES的運用方法與範例, 讓您輕鬆學習上手