MariaDB無法開啟服務

刪除/var/lib/mysql/ib_logfile* /var/lib/mysql/tc.log

Mariadb Server 允許外部設備存取

編輯 /etc/mysql/mariadb.conf.d/50-server.cnf
bind-address            = 0.0.0.0

[PDOException] SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

可能是 查詢 SQL 太長，或是連線逾時

1.修改 MySQL 設定檔
/etc/my.cnf
/etc/my.cnf.d/server.cnf

[mysqld]
max_allowed_packet=16M

2.連線時加入
SET SESSION wait_timeout=65535

MySQL 取得 trigger 作用的 table

SELECT EVENT_OBJECT_TABLE 
INTO @table_name
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_NAME = 'trigger_name';

MySQL 動態 SQL 範例

SET @tab = 'a';
SET @s = CONCAT('select * from ', @tab, ' where ID = 1');

PREPARE stmt1 FROM @s;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

MySQL Table 匯出csv

SELECT *
INTO OUTFILE '/tmp/data.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM table

MySQL流程控制-repeat範例

# 去除字串中重覆空白字元

DROP FUNCTION `mrim`;
CREATE DEFINER=`root`@`localhost` FUNCTION `mrim`(`str` VARCHAR(2048)) RETURNS VARCHAR(2048) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER begin
DECLARE i INT DEFAULT 1;
DECLARE l INT;
DECLARE _str varchar(1024);
DECLARE _s varchar(1024);

set l = LENGTH(str);
set _str = '';

r1:REPEAT

set _s = substr(str,i,1);

if(_s<> ' ') then set _str = concat(_str,_s);
elseif (_s=' ' and substr(_str, LENGTH(_str), 1)<>' ') THEN set _str = CONCAT(_str,_s);
END IF;
  
set i = i + 1;

UNTIL i>l
END REPEAT r1;

return _str;
end

MySQL流程控制-while範例

# 字串反轉

DROP FUNCTION `while_ex`;
CREATE DEFINER=`root`@`localhost` FUNCTION `while_ex`(`str` VARCHAR(255)) RETURNS VARCHAR(255) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER begin
declare _ varchar(255) default '';

while not str is null and str<>'' do
  set _ = concat(_, substr(str, -1,1));
  set str = substr(str,1, length(str)-1);
end while;

return _;
end

MySQL流程控制

IF

CASE

LOOP

LEAVE

ITERATE

REPEAT

WHILE

MySQL 清除 root 密碼

/etc/init.d/mysql stop

/usr/libexec/mysqld --skip-grant-tables --user=root &

mysql -u root

mysql> UPDATE mysql.user SET Password=PASSWORD('') WHERE User='root';

CentOS release 6.5 (64bit) 安裝 MySQL lib_mysqludf_sys 筆記

yum install mysql-devel

yum install git
git clone https://github.com/mysqludf/lib_mysqludf_sys
cd  lib_mysqludf_sys/

修改 Makefile內容
gcc -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o  /usr/lib64/mysql/plugin/lib_mysqludf_sys.so -fPIC

make

mysql -u root mysql < lib_mysqludf_sys.sql

MySQL insert where 範例

create table a (i1 int, i2 int);

insert into a(i1,i2) select 1,2 from dual;

insert into a(i1,i2) select 1,2 from dual where 1=2;

MySQL執行作業系統指令

範例
\! ls -l
system ls -l

MySQL Unix timestamp 相關函式

FROM_UNIXTIME()   Format UNIX timestamp as a date

UNIX_TIMESTAMP() Return a UNIX timestamp

MySQL IP LONG 轉換函式

INET_ATON() Return the numeric value of an IP address


INET_NTOA() Return the IP address from a numeric value

Shell Script 中捉取 MySQL 資料庫中的指定記錄值

使用 -N 及 -s 參數


範例
mysql -u network -pnetwork network -e "select * from ip" -N -s

Recover MySQL Database root password

停止 MySQL 服務
#/etc/init.d/mysql stop

使用以下指令啟動 MySQL 服務
# mysqld_safe --skip-grant-tables &

# mysql mysql
進入 MySQL 的命令提示列後：
UPDATE user SET password=password('new password') where user='root';
FLUSH PRIVILEGES;

MySQL SQL 子查詢應用

MySQL新版本加入子查詢功能後，以往很難處理的SQL，變得簡單容易多了

以下是朋友曾經提出的問題：

資料表 b 裡面，有一個主鍵 id，內容是
  id       name
------------------------------
11111%01   A
11111%02   B
11111%03   C
22222%01   D
22222%02   E (id 規則是以 % 作為分隔成兩個數字
與另外一個資料表 Join，資料表 a 的主索引鍵是 11111 與 22222 ...




問題 1.如何將 b.id 這個欄位拆成兩個欄位
id1      id2    name
------------------------------
11111    %01     a
.....

問題 2.兩者該如何作 Join？

---

解法以 MySQL 5為例
問題 1.Answer：
SELECT substring( b.id, 1, locate( '%', b.id ) -1 ) AS b1, substring( b.id, locate( '%', b.id ) ) AS b2, name AS b3 FROM b

問題 2.Answer：
SELECT *
FROM a, (SELECT substring( b.id, 1, locate( '%', b.id ) -1 ) AS b1, substring( b.id, locate( '%', b.id )) AS b2, name AS b3 FROM b ) AS bb
WHERE a.id = bb.b1

MySQL保留字

以下為MySQL保留字，不能使用下列名稱作為表格名稱或欄位名稱，其中大多數和ANSI SQL92相同，一些保留原因是因為MySQL中使用yacc的語法分析器，若使用函式名稱作為表格／欄位名稱，則函式後接著的"("中間不可空白。

ADD	ALL	ALTER	ANALYZE	AND
AS	ASC	AUTO_INCREMENT
BDB	BEFORE	BERKELEYDB	BETWEEN	BIGINT
BINARY	BLOB	BOTH	BTREE	BY
CASCADE	CASE	CHANGE	CHAR	CHARACTER
CHECK	COLLATE	COLUMN	COLUMNS	CONSTRAINT
CREATE	CROSS	CURRENT_DATE	CURRENT_TIME	CURRENT_TIMESTAMP
DATABASE	DATABASES	DAY_HOUR	DAY_MINUTE	DAY_SECOND
DEC	DECIMAL	DEFAULT	DELAYED	DELETE
DESC	DESCRIBE	DISTINCT	DISTINCTROW	DIV
DOUBLE	DROP
ELSE	ENCLOSED	ERRORS	ESCAPED	EXISTS
EXPLAIN
FALSE	FIELDS	FLOAT	FOR	FORCE
FOREIGN	FROM	FULLTEXT	FUNCTION
GRANT	GROUP
HASH	HAVING	HIGH_PRIORITY	HOUR_MINUTE	HOUR_SECOND
IF
IGNORE	IN	INDEX	INFILE	INNER
INNODB	INSERT	INT	INTEGER	INTERVAL
INTO	IS
JOIN
KEY	KEYS	KILL
LEADING	LEFT	LIKE	LIMIT	LINES
LOAD	LOCALTIME	LOCALTIMESTAMP	LOCK	LONG
LONGBLOB	LONGTEXT	LOW_PRIORITY
MASTER_SERVER_ID	MATCH	MEDIUMBLOB	MEDIUMINT	MEDIUMTEXT
MIDDLEINT	MINUTE_SECOND	MOD	MRG_MYISAM	NATURAL
NOT	NULL	NUMERIC	ON	OPTIMIZE
OPTION	OPTIONALLY	OR	ORDER	OUTER
OUTFILE
PRECISION	PRIMARY	PRIVILEGES	PROCEDURE	PURGE
READ	REAL	REFERENCES	REGEXP	RENAME
REPLACE	REQUIRE	RESTRICT	RETURNS	REVOKE
RIGHT	RLIKE	RTREE
SELECT	SET	SHOW	SMALLINT	SOME
SONAME	SPATIAL	SQL_BIG_RESULT	SQL_CALC_FOUND_ROWS	SQL_SMALL_RESULT
SSL	STARTING	STRAIGHT_JOIN	STRIPED
TABLE	TABLES	TERMINATED	THEN	TINYBLOB
TINYINT	TINYTEXT	TO	TRAILING	TRUE
TYPES
UNION	UNIQUE	UNLOCK	UNSIGNED	UPDATE
USAGE	USE	USER_RESOURCES	USING
VALUES	VARBINARY	VARCHAR	VARCHARACTER	VARYING
WARNINGS	WHEN	WHERE	WITH	WRITE
XOR	YEAR_MONTH	ZEROFILL

以下的符號(來自上表)是被 ANSI SQL 禁止的，但是可以被 MySQL 用於列/表名

ACTION
BIT
DATE
ENUM
NO
TEXT
TIME
TIMESTAMP

MySQL 中 LongBlob 及 LongText 實際的最大長度

MySQL 中使用Blob及Text資料型態來儲存有大資料的欄位，以下是官方文件中各類型資料型態所能儲存的長度

類型	長度
Blob Text	65535個字元(2^16-1)
MediumBlob MediumText	16777215 個字元(2^24-1)
LongBlob LongText	4294967295個字元 (2^32-1)

但規格歸規格，產品不見得和規格相符，資料最大的長度其實是取決於 my.cnf 中 max_allowed_packet 參數設定
MySQL 3.23 最大的值是 16M
MySQL 4.0. 最大的值是 1G
MySQL 5.0. 最大的值是 1G

所以所能儲存的資料長度最大是 16M(MySQL 3.23) 或 1G (MySQL 4.0/5.0)

參考文件：
http://www.mysql.com/news-and-events/newsletter/2003-08/a0000000216.html
http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html