mysql FROM_UNIXTIME

SELECT UNIX_TIMESTAMP('2005-03-27 03:00:00');
1111885200
SELECT UNIX_TIMESTAMP('2005-03-27 02:00:00');
1111885200
SELECT FROM_UNIXTIME(1111885200);
2005-03-27 03:00:00 
-------------------------

select 
FROM_UNIXTIME(SUBSTRING(stime,1,10)),FROM_UNIXTIME(SUBSTRING(stime,1,10)),time
from e;

MySQL MEMORY TABLE

DROP TABLE IF EXISTS `etf50`;
CREATE TABLE `etf50` (
  `SDATE` varchar(10) default NULL,
  `STIME` varchar(10) default NULL,
  `PRICE` varchar(10) default NULL,
  `BID` varchar(10) default NULL,
  `ASK` varchar(10) default NULL,
  `CHANGES` varchar(10) default NULL,
  `VOLUME` varchar(10) default NULL,
  `CUMULATIVE` varchar(10) default NULL,
  `ROWID` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`ROWID`)
) ENGINE=MEMORY AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

修改mysql.ini
max_heap_table_size=2048M 
tmp_table_size=2048M 
重啟mysql

 After using MySQL memory engine table as middle cache table, we got this error when application inserting the data to the table. Seems there is some limits on the table. After searching on Google, we know that we need to change some default settings.

    The global system variable "max_heap_table_size" define the maximum size memeory table can reach. The default values is 16384 (Maybe it's OS dependant). So you can change it with the following steps.

    Run the following command to change it at system level.

set global max_heap_table_size=1048576000

    Then modify the MySQL configuration file, adding a new line at the end. So when next time database get restarted, the change can be permanent.

max_heap_table_size=1048576000

    Finally, you need to reconnect to MySQL, and rebuild all the tables.

ALTER TABLE ... ENGINE MEMORY;

    The last step is to let application reconnect to MySQL database, to take the change effective.

http://www.dbatools.net/experience/mysql_table_is_full.html

mysql 備份及回存

備份

mysqldump dbname -uroot -p --opt > backup.sql

回存

cd C:\Program Files\MySQL\MySQL Server 5.1\bin

mysql -uroot -p dbname < backup.sql
資料來源
http://www.study-area.org/tips/mysql_backup.htm

mysql取小數點後的值

SELECT TRUNCATE(999/1000,3);
0.999

MySQL連線軟體 Navicat for MySQL

http://www.navicat.com.tw/download.html

Navicat for MySQL Lite # 繁體中文版(只供非商業用途)

8.1.20

直接下載

MySQL Union 聯集

(SELECT a FROM t1 WHERE a=10 AND B=1) UNION (SELECT a FROM t2 WHERE a=11 AND B=2)

MySQL MINUS 差集

for Oracle

SELECT member_id, name FROM a
MINUS
SELECT member_id, name FROM b

for MySQL

SELECT PHONE
FROM PHONE_a LEFT JOIN PHONE_b on PHONE_a.PHONE = PHONE_b.PHONE
WHERE PHONE_b.PHONE IS NULL

MySQL intersect 交集

for Oracle

SELECT member_id, name FROM a
INTERSECT
SELECT member_id, name FROM b

forMySQL

SELECT a.member_id, a.name
FROM a INNER JOIN b
USING (member_id, name)