【轉貼】Data truncated for column 'co11' at row 1

【轉貼】Data truncated for column 'co11' at row 1

這是殺小？
這是一個mysql在5.0.2以上新加入的模式Strict Mode 造成的結果



mysql手冊中寫了如下的敘述




MySQL Manual wrote: n MySQL 5.0.2 and up, you can select stricter treatment of input values by using the STRICT_TRANS_TABLES or STRICT_ALL_TABLES SQL modes:

SET sql_mode = 'STRICT_TRANS_TABLES';
SET sql_mode = 'STRICT_ALL_TABLES';

STRICT_TRANS_TABLES enables strict mode for transactional storage engines, and also to some extent for nontransactional engines. It works like this:

*

For transactional storage engines, bad data values occurring anywhere in a statement cause the statement to abort and roll back.
*

For nontransactional storage engines, a statement aborts if the error occurs in the first row to be inserted or updated. (When the error occurs in the first row, the statement can be aborted to leave the table unchanged, just as for a transactional table.) Errors in rows after the first do not abort the statement, because the table has already been changed by the first row. Instead, bad data values are adjusted and result in warnings rather than errors. In other words, with STRICT_TRANS_TABLES, a wrong value causes MySQL to roll back all updates done so far, if that can be done without changing the table. But once the table has been changed, further errors result in adjustments and warnings.

For even stricter checking, enable STRICT_ALL_TABLES. This is the same as STRICT_TRANS_TABLES except that for nontransactional storage engines, errors abort the statement even for bad data in rows following the first row. This means that if an error occurs partway through a multiple-row insert or update for a nontransactional table, a partial update results. Earlier rows are inserted or updated, but those from the point of the error on are not. To avoid this for nontransactional tables, either use single-row statements or else use STRICT_TRANS_TABLES if conversion warnings rather than errors are acceptable. To avoid problems in the first place, do not use MySQL to check column content. It is safest (and often faster) to let the application ensure that it passes only legal values to the database.

With either of the strict mode options, you can cause errors to be treated as warnings by using INSERT IGNORE or UPDATE IGNORE rather than INSERT or UPDATE without IGNORE.

大意是這是對支援交易機制的一個新模式,簡單的說任何不合乎欄位設定的格式都會被幹掉
例如如果設定null的欄位,如果寫入的字串是'',那很抱歉 他會靠北出標題列那串字串給你
你一定要寫null才可以反之亦然,最機車的一點是,text屬性不得預設任何字元
是不是覺得很靠北..因為是針對交易機制引擎做的,但是如果好死不死你的程式不用交易(ex.用myisam的程式)那不就靠北了嗎？
如何判斷有沒有開啟這個模式呢
SHOW VARIABLES LIKE 'sql_mode'
如果出現
STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
那就恭喜你啦
解決方案
1.去my.ini or my.cnf改以下設定
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
2. 我比較推薦這個,因為有可能你動不了這台mysql server
在最初使的連線設定上設定空白
mysql_query("SET @@sql_mode = ''");