MySQL

sql_mode

http://xstarcd.github.io/wiki/MySQL/MySQL-sql-mode.html http://www.codedata.com.tw/database/mysql-tutorial-18-errors-warnings/

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

SET GLOBAL sql_mode = 'modes...';
SET SESSION sql_mode = 'modes...';

my.cnf中配置sql-mode
[mysqld]
#set the SQL mode to strict
#sql-mode="modes..."
sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

MySQL日志格式 binlog_format

http://blog.csdn.net/mycwq/article/details/17136997

若沒設定好，會報以下錯誤
Warning: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.

在配置文件中設定主從復制模式( 需重啟生效 )：
log-bin=mysql-bin
#binlog_format="STATEMENT"
#binlog_format="ROW"
binlog_format="MIXED"
也可以在運行時動態修改binlog的格式。例如：

mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';

centos install http://kikinote.net/article/1311.html

同key update不然則insert

INSERT INTO tableName (memberID, deviceID, status, loginTime) VALUES(:memberID, :deviceID, 1, :now)
ON DUPLICATE KEY
UPDATE status = 1, loginTime = :now;

call sp

call usp_GetEmployeeName(103, @name);