mysql 常用指令

重新啟用
# service mysql restart
# /etc/rc.d/init.d/mysqld restart

將欄位 a 的 varchar 型態轉成 int 進行排序
select * from test ORDER BY CAST(`a` AS DECIMAL(10,2)) DESC

匯出
> mysqldump -u root -p --quick --default-character-set=utf8 --complete-insert --add-drop-table --no-create-info --no-data --compress --result-file=/home/sean/comtrend1.sql comtrend table

匯入
1)先新增DB
> /usr/local/mysql/bin/mysqladmin -u root -p create comtrend
2)將結構跟資料匯入
> /usr/local/mysql/bin/mysql -u root -p comtrend < /home/sean/comtrend1.sql

TRUNCATE TABLE `authors`

GRANT SELECT,DELETE,INSERT,UPDATE ON itry.* TO itry@localhost IDENTIFIED BY 'itryedcrfv';

FLUSH PRIVILEGES;

SET PASSWORD FOR 'root'@ 'localhost' = PASSWORD( '*****' )

4.1 版之前的舊編碼方式
SET PASSWORD FOR 'root'@ 'localhost' = OLD_PASSWORD( '*****' )

SELECT * FROM `members` WHERE `user` LIKE CONVERT( _utf8 'sean' USING big5 ) COLLATE big5_chinese_ci LIMIT 0 , 30

SELECT * FROM table_references WHERE where_definition ORDER BY RAND()

修復資料表
REPAIR TABLE phpbb_search_wordlist

ALTER TABLE table_name ADD COLUMN column_name type AFTER column_name1

ALTER TABLE table_name CHANGE COLUMN column_name column_name1 type

重設自動新增的數值
ALTER TABLE `pweb_categories` PACK_KEYS =0 CHECKSUM =0 DELAY_KEY_WRITE =0 AUTO_INCREMENT =1

可加快 mysql 的設定 /etc/my.cnf
max_connect_errors = 1844674407370954751
connect_timeout = 20
skip-name-resolve
(* 另一個解法：在 /etc/hosts中加入新加的 Web 內網 ip 主機名稱)

#skip-networking
#skip-external-locking

slave_net_timeout = 30