Jason 資料庫

MySQL需注意的幾點tips

1、用mysql內置函數轉換ip地址和數字
利用兩個內置函數
inet_aton:將ip地址轉換成數字型
inet_ntoa:將數字型轉換成ip地址

2、充分利用mysql內置的format函數
尤其是在處理字符格式的時候,例如將12345轉換成12,345這樣的,只要用:format(12345,0)即可,如果用format(12345,2)則顯示的是12,345.00了…

3、利用mysql的內置函數處理時間戳問題
eg : select FROM_UNIXTIME(UNIX_TIMESTAMP(),’%Y %D %M %h:%i:%s %x’);
結果: 2004 3rd August 03:35:48 2004

4、利用mysql_convert_table_format轉換表類型
需要DBI和DBD的mysql相關模組支援才能用,例子:
mysql_convert_table_format –user=root –password=’xx’ –type=myisam test yejr

5、修改mysql表中的字段名
alter table tb_name change old_col new_col definition…

6、利用臨時變數
select @var1:=a1+a2 as a_sum,@var2:=b1+b2 as b_sum,@var1+@var2 as total_sum from test_table xxx;

7、用int類型存儲ip地址
原先錯誤的認為必須用bigint才夠，後來發現使用int unsigned類型就足夠了。 :)

8、CREATE TABLE IF NOT EXISTS … select 語法局限
儘管只是對目標表的insert操作，但是‘居然’不允許源表的insert操作，真是莫名其妙

9、利用IF函數快速修改ENUM字段值
一個例子：
update rule set enable = if(‘0′ = enable,’1′,’0’) where xxx;
enable 類型：enum(‘0′,’1’) not null default ‘0’

10、事務無法嵌套

11、避免長時間的sleep連接造成的連接數超出問題
設定全局變數 wait_timeout 和 interactive_timeout 為比較小的值，例如 10(s)，就能使每個sleep連接在10s之後如果還沒有查詢的話自動斷開。

Jason 美麗花蝴蝶

我說了

真不敢相信,我竟然在MSN上問了….
==============================
我:”我可不可以好奇的問個問題呀”
VV:”你這樣說好恐怖喔”
VV:”ㄎ”
我:”不恐怖啦”
我:”我只是想問，為什麼電腦的事不找我幫忙”
VV:”喔 這個喔”
VV:”ㄎㄎ”
VV:”這種苦差事 當然要麻煩華特阿”
我:”怕麻煩我的意思？”
VV:”對阿 呵呵”
我:”我還以為我只是個司機而已”
VV:”因為我跟小rita的反應都是這樣 呵呵”
VV:”什麼司機 吼 說那麼難聽”
==============================
還好,沒有爆出什麼奇怪的話…

Jason 程式設計

使用 ip2nation 取得 IP 位址所在的國家別

http://www.neo.com.tw/archives/000980.html

ip2nation 已經把 IP 所在的國別整理成 SQL 語法，只要下載下來放進資料庫即可:
http://www.ip2nation.com/ip2nation/Download


(IP 資料庫最後更新日期是上個月，應該有在持續更新)

放進資料庫之後，要使用的話必須把 IP 轉為數字進行查詢，ip2nation 有提供 PHP 的範例:

$server = ”; // MySQL hostname
$username = ”; // MySQL username
$password = ”; // MySQL password
$dbname = ”; // MySQL db name

$db = mysql_connect($server, $username, $password) or die(mysql_error());
mysql_select_db($dbname) or die(mysql_error());

$sql = ‘SELECT c.country
FROM ip2nationCountries c, ip2nation i
WHERE
i.ip < INET_ATON(“‘.$_SERVER[‘REMOTE_ADDR’].'”)
AND c.code = i.country
ORDER BY i.ip DESC LIMIT 0,1′;

list($countryName) = mysql_fetch_row(mysql_query($sql));

// Output full country name
echo $countryName;

(使用 MySQL 的 INET_ATON() 函式把 IP 轉為數字) 如果不是用 MySQL 就必須自行做 IP 轉換的計算，計算的方式以 261.230.14.128 為例:
261×2563 + 230×2562 + 14×256 + 128

PHP 的轉換範例:

$ip = split (“\.”, “261.230.14.128”);
echo $ip[3] + $ip[2]*256 + $ip[1]*256*256 + $ip[0]*256*256*256;

ip2nation 網址:
http://www.ip2nation.com/

Jason 軟體

農民曆和國曆換算

我老爸不知從哪聽來的,人的國曆和農曆生日19年會重疊一次,
就叫我查查是否屬實(因為我是寫軟体的),
我記得有在WEB上看農曆的萬年曆,就上去查看看吧!!
http://www.cathvoice.org.tw/search/tool.htm
http://www.sinica.edu.tw/ftms/luso.htm

實際用我老爸生日試過之後果然是19年一次,但也只有第一次19年是如此,
但之後的19年,都差一天,
但…..用我的來試,卻又不太相同,總會差個幾天.

以下介紹所謂的19年現象：
http://www.onlinetest.idv.tw/bbs4/topic.asp?TOPIC_ID=2957&FORUM_ID=36&CAT_ID=7

以下是相關的資料
http://www.purecs.net/thread/topic457_2.aspx
http://www.sinica.edu.tw/~tdbproj/sinocal/lusodoc.html

補充一點,.NET2.0居然有天干地支的類別(EastAsianLunisolarCalendar),真是太神奇了
http://msdn2.microsoft.com/zh-tw/library/system.globalization.eastasianlunisolarcalendar(VS.80).aspx

Jason 資料庫

MySQL的數值類型

11.2. 數值類型

MySQL支援所有標準SQL數值資料類型。這些類型包括嚴格數值資料類型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似數值資料類型(FLOAT、REAL和DOUBLE PRECISION)。關鍵字INT是INTEGER的同義詞，關鍵字DEC是DECIMAL的同義詞。

BIT資料類型保存位欄位值，並且支援MyISAM、MEMORY、InnoDB和BDB表。

作為SQL標準的擴展，MySQL也支援整數類型TINYINT、MEDIUMINT和BIGINT。下面的表顯示了需要的每個整數類型的存儲和範圍。

類型

位元組

最小值

最大值

 

 

(帶符號的/無符號的)

(帶符號的/無符號的)

TINYINT

1

-128

127

 

 

0

255

SMALLINT

2

-32768

32767

 

 

0

65535

MEDIUMINT

3

-8388608

8388607

 

 

0

16777215

INT

4

-2147483648

2147483647

 

 

0

4294967295

BIGINT

8

-9223372036854775808

9223372036854775807

 

 

0

18446744073709551615

MySQL還支持選擇在該類型關鍵字後面的括弧內指定整數值的顯示寬度(例如，INT(4))。該可選顯示寬度規定用於顯示寬度小於指定的列寬度的值時從左側填滿寬度。

顯示寬度並不限制可以在列內保存的值的範圍，也不限制超過列的指定寬度的值的顯示。

當結合可選擴展屬性ZEROFILL使用時， 默認補充的空格用零代替。例如，對於聲明為INT(5) ZEROFILL的列，值4檢索為00004。請注意如果在整數列保存超過顯示寬度的一個值，當MySQL為複雜聯接生成臨時表時會遇到問題，因為在這些情況下MySQL相信資料適合原列寬度。

所有整數類型可以有一個可選(非標準)屬性UNSIGNED。當你想要在列內只允許非負數和該列需要較大的上限數值範圍時可以使用無符號值。

浮點和定點類型也可以為UNSIGNED。同數類型，該屬性防止負值保存到列中。然而，與整數類型不同的是，列值的上範圍保持不變。

如果為一個數值列指定ZEROFILL，MySQL自動為該列添加UNSIGNED屬性。

對於浮點列類型，在MySQL中單精確度值使用4個位元組，雙精度值使用8個位元組。

FLOAT類型用於表示近似數值資料類型。SQL標準允許在關鍵字FLOAT後面的括弧內選擇用位指定精度(但不能為指數範圍)。MySQL還支援可選的只用於確定存儲大小的精度規定。0到23的精度對應FLOAT列的4位元組單精確度。24到53的精度對應DOUBLE列的8位元組雙精度。

MySQL允許使用非標準語法：FLOAT(M,D)或REAL(M,D)或DOUBLE PRECISION(M,D)。這裏，“(M,D)”表示該值一共顯示M位元整數，其中D位元位於小數點後面。例如，定義為FLOAT(7,4)的一個列可以顯示為-999.9999。MySQL保存值時進行四捨五入，因此如果在FLOAT(7,4)列內插入999.00009，近似結果是999.0001。

MySQL將DOUBLE視為DOUBLE PRECISION(非標準擴展)的同義詞。MySQL還將REAL視為DOUBLE PRECISION(非標準擴展)的同義詞，除非SQL伺服器模式包括REAL_AS_FLOAT選項。

為了保證最大可能的可攜性，需要使用近似數值資料值存儲的代碼應使用FLOAT或DOUBLE PRECISION，不規定精度或位數。

DECIMAL和NUMERIC類型在MySQL中視為相同的類型。它們用於保存必須為確切精度的值，例如貨幣資料。當聲明該類型的列時，可以(並且通常要)指定精度和標度；例如：

salary DECIMAL(5,2)

在該例子中，5是精度，2是標度。精度表示保存值的主要位數，標度表示小數點後面可以保存的位數。

在MySQL 5.1中以二進位格式保存DECIMAL和NUMERIC值。

標準SQL要求salary列能夠用5位元整數位元和兩位元小數保存任何值。因此，在這種情況下可以保存在salary列的值的範圍是從-999.99到999.99。

在標準SQL中，語法DECIMAL(M)等價於DECIMAL(M,0)。同樣，語法DECIMAL等價於DECIMAL(M,0)，可以通過計算確定M的值。在MySQL 5.1中支援DECIMAL和NUMERIC資料類型的變數形式。M預設值是10。

DECIMAL或NUMERIC的最大位數是65，但具體的DECIMAL或NUMERIC列的實際範圍受具體列的精度或標度約束。如果此類列分配的值小數點後面的位數超過指定的標度允許的範圍，值被轉換為該標度。(具體操作與作業系統有關，但一般結果均被截取到允許的位數）。

BIT資料類型可用來保存位欄位值。BIT(M)類型允許存儲M位元值。M範圍為1到64。

要指定位值，可以使用b’value‘符。value是一個用0和1編寫的二進位值。例如，b’111′和b’100000000′分別表示7和128。參見9.1.5節，“位欄位值”。

如果為BIT(M)列分配的值的長度小於M位，在值的左邊用0填充。例如，為BIT(6)列分配一個值b’101′，其效果與分配b’000101′相同。

當要在一個數值列內保存一個超出該列允許範圍的值時，MySQL的操作取決於此時有效的SQL模式。如果模式未設置，MySQL將值裁剪到範圍的相應端點，並保存裁減好的值。但是，如果模式設置為traditional(“嚴格模式”)，超出範圍的值將被拒絕並提示錯誤，並且根據SQL標準插入會失敗。參見5.3.2節，“SQL伺服器模式”。

如果INT列是UNSIGNED，列範圍的大小相同，但其端點會變為到0和4294967295。如果你試圖保存-9999999999和9999999999，以非嚴格模式保存到列中的值是0和4294967296。

如果在浮點或定點列中分配的值超過指定(或默認)精度和標度規定的範圍，MySQL以非嚴格模式保存表示範圍相應端點的值。

當MySQL沒有工作在嚴格模式時，對於ALTER TABLE、LOAD DATA INFILE、UPDATE和多行INSERT語句，由於裁剪發生的轉換將報告為警告。當MySQL工作在嚴格模式時，這些語句將失敗，並且部分或全部值不會插入或更改，取決於是否表為事務表和其他因素。詳情參見5.3.2節，“SQL伺服器模式”。