Category: sql

[DataBase] 備份 MySQL 3.23.49 轉到 MySQL 5 utf8 轉換到 utf8

最近在幫友人處理他們web跟資料庫的轉移，然後發現他們的資料庫是很古早的資料庫 MySQL 3.23.49 版本，相當古老吧，因為那個時候我才正值大二時代，剛開始接觸電腦的時候而已，連最基本的資料庫都不知道是什麼，哈哈經過這麼久終於發展到 MySQL 5版本了，不過從 MySQL 4.x 開始支援的 Unicode 系統，所以當時也有很多人遇到轉換問題，我今天也遇到相同問題，不過是在轉換到 Linux Centos 5.1 版本底下，原本機器使用 Red Hat Linux 7.3，真是一個很舊的版本，因為 Red Hat Linux 已經到 9.0 版本了，而且目前不維護了。 之前版本轉換都沒有什麼問題，就是 4.x 轉到 5.x 版本，參可我之前寫的這篇 [Mysql] 資料庫備份[big5]utf8轉換成utf-8，如果這篇文章解決不了的話，那就可能用我底下的方法了，不過過上面那個方法不適合用在 3.23 轉到 5.x 版，因為還是會發生亂碼現象，可是這只會發生在 Linux 底下，因為我用 FreeBSD 7.0 R 版，在這環境底下是沒有問題的，所以今天很囧，都在處理 Linux 的部份，哈哈，所以大家還是去用 FreeBSD 吧 Continue reading “[DataBase] 備份 MySQL 3.23.49 轉到 MySQL 5 utf8 轉換到 utf8”

[MySQL] outer join 使用

之前我遇到問題，有兩個表格 a 跟 b，分別利用 id 當作連接，當你使用 where a.id = b.id 的時候，當 b 資料表沒有對應到的時候，撈出來的資料就會少一筆，問題如下
: 想請教各位大大， : 如果我現在有兩個table t1,t2 : Table t1: : uid INT : name NCHAR(10) : Table t2: : uid INT : t1_id INT 參考到t1.uid : 我下一個SQL query: : SELECT t1.name, COUNT(t2.uid) : FROM t1,t2 : WHERE t2.t1_id=t1.uid : GROUP BY t1.name : 這樣會計算出每個t1.name項目在t2中所出現的次數。 : 但是如果次數為零時就不會顯示出來。 : 想請教大家，怎樣修改可以讓次數為零的t1.name也顯示出來呢?
解決方法：就是利用 outer join 
$sql = "SELECT t1.t_name, count(t2.uid) as aa FROM " . $xoopsDB->prefix('teacher') . " as t1 LEFT OUTER JOIN " . $xoopsDB->prefix('student') . " as t2  on t1.tid = t2.st_teacher group by t1.t_name";
Xoops 寫法

[MySQL] 內建函式 MySQL Replace 功能

今天老師寄信給我，說我轉移的 journal.CN 的文章，出現以前的文章，不能看到圖片，我去查了一下，發現 AMS 文章系統的編輯軟體，相當爛，它的插入圖片，竟然是用絕對網址，然後他會先讀取 mainfile.php 裡面的 
define('XOOPS_URL', 'http://journal.cn.ee.ccu.edu.tw');
然後把圖片的網址寫死，而不是動態的，就是利用相對路徑，所以我利用了 replace 的功能來解決全部文章圖片的問題，語法如下 
update `xoops_ams_text` set `hometext` = replace(`hometext`,'140.123.107.38/~cnews','journal.cn.ee.ccu.edu.tw');
上面是說，取代 hometext 這個欄位的，找到 140.123.107.38/~cnews 取代成 journal.cn.ee.ccu.edu.tw 這語法相當好用，大家可以嘗試看看，畢竟如果用 php 的 replace 語法，還要利用陣列方式，比較麻煩

[BCB] 如何利用連結 MySQL 5以上跟 MSSQL Server

這兩天開始玩 BCB 的這東西，其實我原本就有打算要學一套視窗軟體，畢竟好像還不錯，可以寫寫軟體，所以就拿了 BCB 來學習。 剛開始想說寫個要跟 MSSQL 資料庫的統計圖，發現 BCB 並不支援 MSSQL，解決方式，當然就是拿 Delphi 的元件 利用 Delphi 7 的 MSSQL Driver Update 內的「dbexpmss.dll」 Copy 到「 $(BCB)\BIN 」 然後在設定 dbxconnections.ini
[MSSQL] GetDriverFunc=getSQLDriverMSSQL LibraryName=dbexpmss.dll VendorLib=oledb HostName=ServerName DataBase=Database Name User_Name=user Password=password BlobSize=-1 ErrorResourceFile= LocaleCode=0000 MSSQL TransIsolation=ReadCommited OS Authentication=False
然後再來是 MySQL5，原本的 BCB 並不支援 MSQL5 所以自己另外找了文章 http://www.justsoftwaresolutions.co.uk/delphi/dbexpress_and_mysql_5.html

[MySQL] 取代部份字串問題

剛剛在 ptt 遇到之前當兵的工作同事，話說他要去中華電信工作了，還真是不錯，他在 database 版問一個取代的問題，然後我幫他找一下答案，解決了，這可以用 sql 語法解決，或者是用程式都可以 問題：
在MySQL 我有大約50000筆資料 有個欄位像這樣 0040000001~0040050000 現在因故要把 004改成005 變成像這樣 0050000001~0050050000
我的解法： 
UPDATE `test` SET `test` = CONCAT( '005', SUBSTRING( `test` , 4 ) )  WHERE
SUBSTRING( `test` , 1, 3 ) = '004'
有更快解法嗎？ REPLACE 好像不行，因為後面數字也會被改到，不過還是主要用 SUBSTRING

[MYSQL] 忘記 root 密碼，登不進去 phpMyAdmin 教學

2011.06.24 Update: 修改語法 剛剛在處理老闆的機器移機問題，雖然有給我 root 權限，可是 MySQL 密碼沒有給我 root 的，然後我去看程式，都沒有用到 root ，都是用普通使用者，所以就上網找一下忘記密碼怎麼處理，其實找到的方法就是利用 command line 的方法，相當方便，最終解決方法還是文字介面，作法如下 首先先 Kill 掉所有 MySQL 的連線 
# on Linux
/etc/init.d/mysqld stop
# on FreeBSD
/usr/local/etc/rc.d/mysql-server stop
killall -9 mysqld
然後進入 MySQL 安全模式 
mysqld_safe -u root --skip-grant-tables &
然後利用文字介面修改 MySQL root 密碼 
$ mysql -u root -p
> use mysql;
> UPDATE user SET password=password('這裡輸入你的密碼') where user='root';
> FLUSH PRIVILEGES;
> exit;

dump database

$ mysqladmin -uroot -p flush-logs
$ mysqldump -B -uroot -p --opt phpbb2 > phpbb2_20020601.sql --databases 或 -B 日後會自動建立該資料庫

dump table

mysqldump phpbb2 -uroot -p --opt phpbb2_users > phpbb2_users_20020601.sql

DB backup

如果另外一台電腦上沒有phpbb2這個DB記得要新增一個 
mysql -uroot -p -e "CREATE DATABASE phpbb2"
then mysql phpbb2 -uroot -p < phpbb2_20020601.sql[/code] Reference http://www.study-area.org/tips/mysql_backup.htm

[SQL] 隨機選取資料 (MSSQL,ACCESS,MYSQL)

這個功能還蠻需要的，底下就是三個例子 以下方法，可以幫助隨機取得廣告資料、最新消息等，隨機產生資料。 使用 SQL 語法的 TOP n 來指定取得筆數，再用 ORDER BY 的方式，來亂數取得資料，並排序。 
MS SQL：SELECT TOP 1 * FROM Table WHERE 條件 ORDER BY NEWID()

ACCESS：SELECT TOP 1 * FROM Table WHERE 條件 ORDER BY RND(數字欄位名稱)

MYSQL：SELECT * FROM Table ORDER BY RAND() LIMIT 1
http://blog.xuite.net/jameschih/java/8308864

[Mysql] 資料庫備份[big5]utf8轉換成utf-8

其實在很多opensource底下的套裝軟體，資料庫預設都是用 utf8，我想這會造成在 phpMyAdmin 底下看到亂碼，然後自己之前也有遇到問題，然後又在網路上看到這篇 搶救 xdite.net 所用的奇技淫巧 裡面所寫的備份方式跟我在轉換 phpBB2 跟自己的 wordpress 一樣 大同小異，我還在想說有更好的解法說，看來是沒有，在 wordpress 底下，只能利用後台的資料庫備份，不然用phpMyAdmin的話，我想你備份出來也是沒用。 Continue reading “[Mysql] 資料庫備份[big5]utf8轉換成utf-8”