經驗交流分享與備忘

在 MySQL 資料庫中想產生一組獨一無二的 key，這樣可以做為比對的依據。打算直接在資料庫上操作新增欄位，至於 key 的產生是很直覺地想用 md5() 這個 function 來處理。但是因為要拿什麼資料來雜湊呢? 想說就用其他欄位加一加，再加上時間來湊。

很直覺地寫下這樣的 query

SELECT md5( id + name + now() )

可惜這樣的結果會因為 id 是 int，而且 name 是 string 而產生非預期的結果。解決方法是把所有的欄位轉成 string 來處理，這時候需要 concat() 來組合字串。

SELECT md5( concat( id, name, now() ) )

這樣就可以得到正確的 md5 值了。

deaf03c84d18a56b70e0b7d6438ada3e

以前寫抽獎程式時 ,用了很笨的方式, 把所有的資料撈出來, 然後, 再用 PHP 的 RAND 函數去抽取資料.

其實, 根本不要這麼麻煩, 因為, 這些動作, 在 SQL 語法就可以輕鬆達成.

如何做?

寫一行SQL

Three options for MySQL:

1) create the new database, bring down the server, move the files from one database folder to the other, and restart the server. note that this will only work if ALL of your tables are myisam.

2) create the new database, use CREATE TABLE ... LIKE statements, then use INSERT ... SELECT * FROM statements.

3) use mysqldump and reload with that file.

MySQL is a popular open source enterprise ready database. There are many tools available for interacting with this database, but as with most enterprise databases most access happens via Structured Query Language. There are a few options to export data to CSV from MySQL. Some require third part tools, while another uses a command line tool (useful on Unix machines) and a final option via SQL. Lets take a look at the last two options:

MySQL command line tool (on Unix):

mysql -u exampleuser -p letmein exampledb -B -e "select * from
\'person\';" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > filename.csv

So what does all of that mean?

1. mysql : the executable file (should be where MySQL is installed)
2. -u exampleuser : a switch and option for the username to execute the SQL with
3. -p letmein : a switch and option for the password of the user
4. exampledb : the database to run the SQL against
5. -B : instructs that the output should be tab delimited (we will convert this to commas later in the command)
6. -e “the sql statement here” : the SQL statement to run returning your data
7. | sed ’s/\t/”,”/g;s/^/”/;s/$/”/;s/\n//g’ : sed is a Unix stream processor, essentially allow for transformations in this case. Here we have four sed commands that change the tabs to double quotes, adds double quotes to the beginning and end of each line and adds a new line marker at the end of each line.
8. > filename.csv : outputs the results to the file named filename.csv

MySQL SQL Syntax:

SELECT column_a,column_b,column_c+column_d INTO OUTFILE '/tmp/result.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

The MySQL select statement is using an INTO OUTFILE command to output the results of the SQL select into a file (in this case /tmp/result.csv).

A couple of notes:

1. In both cases the file is saved to a location local to where the database is being ran from (on the same machine, or to a mount/drive available to the machine).
2. The header row will need to be added to both files (which can be done using any text editor or spreadsheet program).

back to top

最近因為接手維護 & 開發舊的系統, 所以必須熟悉 前人寫的 SQL語法, 前輩習慣使用一串SQL語法,把所有的資料撈出來 ,這樣節省很多時間 ,速度也會快很多.
但是, 對於維護的人來說 ,剛開始真是痛苦啊 ...其中, 使用很多的 LEFT JOIN 合併資料, 我覺得很方便 ,但是不熟悉 會搞錯, 所以慢慢的會把相關的心得整理
作為資料參考備忘 放在這裡 ,歡迎交流討論喔 !!

以下，是國外論壇的一篇發問, 關於如何使用兩個以上的 LEFT JOIN ..

The inefficiency of the IN clause in SQL statement is well known. Could anyone suggest some workarounds for the following case.

當一段 SQL 寫成如下:

update TABLE1 set Fields1 = ((select Fields1 from TABLE1 where Fields2 = (select Fields3 from TABLE2 where Fields2=ID )) + NUM) where Fields2 = (select Fields3 from TABLE2 where Fields2 = ID)

被執行時, 在 MySQL 會回應

You can't specify target table 'table name' for update in FROM clause .

解決方式:

CONVERT_TZ() 將時間日期值dt 從from_tz 給出的時區轉到to_tz給出的時區，然後返回結果值。
關於可能指定的時區的詳細論述。若引數無效，則這個函數會返回 NULL。

在從若from_tz 到UTC的轉化過程中，該值超出 TIMESTAMP 類型的被支持範圍，那麼轉化不會發生。關於 TIMESTAMP 範圍的論述。

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');

        -> '2004-01-01 13:00:00'

Description:
When you create a trigger without specifying the BEGIN/END mysqldump will not dump correctly.

How to repeat:
Step 1

這個案例是寫給那些不小心忘掉 root 密碼的人、不小心把 root 刪掉的人、不小心把 root 權限改掉的人… (總而言之，都是一些不小心對 MySQL 做了蠢事的人 :p) 用的，告訴他們比把 MySQL 砍掉重練還要快的方法。

下面是以更改 root 的密碼為範例，要做其他事情的人請自己參考並且更改內容。

條件：

• 你必須有那台機器的 shell access 並且可以啟動 mysqld (如果你是用 UNIX)
• 你必須會使用 command line 的 mysql 介面，並且會輸入 sql 指令

When using mysqlimport to import data from the command line into the popular open-source MySQL database server there are a few errors that can occur. This article covers some of these errors and solutions for them.

In the examples on this page it is assumed the mysqlimport command is in the search path, and the full path to the executable is not shown. We are importing data into the customer table in the test database. The basic usage for the mysqlimport command is:

mysqlimport -u username -p database /path/to/tablename.txt

Note that the file must be the same as the tablename (and it is case-sensitive) but the extension part of the filename can be whatever you would like it to be.

In our examples we would be issuing the following command:

Mysql 5.1.22 Replication Master to Slave

預設環境
Master = 192.168.1.102
Slave = 192.168.1.103

工作日志之-MySQL slave Replication Error

Description：

mysql> start slave;
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log

[root@slave ~]# tail /var/log/mysqld.log