MySQL 資料型態的限制

CHAR	固定長度 0 到 255 字
VARCHAR	變動長度 0 到 255 字
TINYTEXT	255 字
TEXT,BLOB	64Kib 字
MEDIUMTEXT,MEDIUMBLOB	16Mib 字
LONGTEXT,LONGBLOB	4Gib 字
TINYINT	-128 to 127 或 0 to 255無符號
SMALLINT	-32768 to 32767 或 0 to 65535無符號
MEDIUMINT	-8388608 to 8388607 或 0 to 16777215無符號
INT	-2147483648 to 2147483647 或 0 to 4294967295無符號
BIGINT	-9223372036854775808 to 9223372036854775807 normal
FLOAT	A small number with a floating decimal point.
DOUBLE	A large number with a floating decimal point.
DECIMAL	A DOUBLE stored as a string , allowing for a fixed decimal point.
DATE	YYYY-MM-DD.
DATETIME	YYYY-MM-DD HH:MM:SS.
TIMESTAMP	YYYYMMDDHHMMSS.
TIME	HH:MM:SS.
ENUM	Short for ENUMERATION which means that each column may have one of a specified possible values.
SET	Similar to ENUM except each column may have more than one of the specified possible values.

MYSQL strip_tags

這是從MySQL官方上看到的資料，因為最近剛好有這個需求。

可以省下寫程式的時間，相當方便！！

delimiter ||
DROP FUNCTION IF EXISTS strip_tags||
CREATE FUNCTION strip_tags( x longtext) RETURNS longtext
LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA 
BEGIN
DECLARE sstart INT UNSIGNED;
DECLARE ends INT UNSIGNED;
SET sstart = LOCATE('<', x, 1);
REPEAT
SET ends = LOCATE('>', x, sstart);
SET x = CONCAT(SUBSTRING( x, 1 ,sstart -1) ,SUBSTRING(x, ends +1 )) ; 
SET sstart = LOCATE('<', x, 1);
UNTIL sstart < 1 END REPEAT;
return x;
END;
||
delimiter ;

Javascript Datediff & string to integer

Date.prototype.dateDiff = function(interval,objDate){

//若參數不足或 objDate 不是日期物件則回傳 undefined

if(arguments.length<2||objDate.constructor!=Date) return undefined;

switch (interval) {

//計算秒差

case "s":return parseInt((objDate-this)/1000);

//計算分差

case "n":return parseInt((objDate-this)/60000);

//計算時差

case "h":return parseInt((objDate-this)/3600000);

//計算日差

case "d":return parseInt((objDate-this)/86400000);

//計算週差

case "w":return parseInt((objDate-this)/(86400000*7));

//計算月差

case "m":return (objDate.getMonth()+1)+((objDate.getFullYear()-this.getFullYear())*12)-(this.getMonth()+1);

//計算年差

case "y":return objDate.getFullYear()-this.getFullYear();

//輸入有誤

default:return undefined;

}

}

Date.prototype.val = function(y,m,d){
if(y!=null && d!=null && d!=null){
return this.setFullYear(y,m,d);
}else{
return this.getFullYear()+'/'+(this.getMonth()+1)+'/'+this.getDate();
}
} 

第一個從網路找來的，寫習慣了ASP，沒這個感覺很怪


第二個是模擬val()，寫習慣JQUERY都會用這個，
字串數值互轉

parseInt(str,10);

.toString

JAVASCRIP 取消選取效果

取消選取的效果

onselectstart="return false;"
onmousedown="if (typeof event.preventDefault != 'undefined') {
event.preventDefault();}"

javascript windows onload event

var onload_before = window.onload || function () {};
window.onload = function ()
{
  onload_before();
// next..
}

MySQL 字串合併、字串連接 語法 concat substring

篩選或是更新資料的時候，常有需要用到補上字串，或是合併欄位的情況。

這時候使用update table set a=a+b 是不行的，要改用concat ，另外可以用SUBSTRING 來作截字的動作

例如

update table set a=concat(a,b);

update table set a=concat(a,'文字');

SELECT SUBSTRING('Quadratically',5);

 

參考資料如下

CONCAT(str1,str2,...)

Returns the string that results from concatenating the arguments. May have one or more arguments. If all arguments are non-binary strings, the result is a non-binary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent binary string form; if you want to avoid that, you can use an explicit type cast, as in this example:

SELECT CONCAT(CAST(int_col AS CHAR), char_col);

CONCAT() returns NULL if any argument is NULL.

mysql> SELECT CONCAT('My', 'S', 'QL');
        -> 'MySQL'
mysql> SELECT CONCAT('My', NULL, 'QL');
        -> NULL
mysql> SELECT CONCAT(14.3);
        -> '14.3'

SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)

The forms without a len argument return a substring from string str starting at position pos. The forms with a len argument return a substring lenstr, starting at position pos. The forms that use FROM are standard SQL syntax. It is also possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. A negative value may be used for pos in any of the forms of this function. characters long from string

For all forms of SUBSTRING(), the position of the first character in the string from which the substring is to be extracted is reckoned as 1.

mysql> SELECT SUBSTRING('Quadratically',5);
        -> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
        -> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
        -> 'ratica'        
mysql> SELECT SUBSTRING('Sakila', -3);
        -> 'ila'        
mysql> SELECT SUBSTRING('Sakila', -5, 3);
        -> 'aki'
mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
        -> 'ki'

MySQL 常用維護指令整理

1.更改預設排序方式:
ALTER TABLE `tablename` ORDER BY `id`
也可以是多個排序值

2.修復資料表:
REPAIR TABLE `tablename`

3.重新讀取權限FLUSH PRIVILEGES ;