mysql相关知识

如果一个表存在则删除： drop table if exit table_name; 添加索引： alter table add index(col_name);

评论

14 楼 liudaoru 2010-04-26  

set character_set_database=gbk;set names 'gbk';

13 楼 liudaoru 2009-04-27  

MySQL编码转码：latin1转为utf8

From：http://chinaonrails.com/topic/view/1205.html

好多人应该都碰到过MySQL中文乱码问题，应该也都尝试过很多方法。今天在此转篇很实用的文章，希望对需要的有所帮助。

MySQL latin1 轉 utf8
http://a-wei.net/archives/4

a. 把資料庫 dump 出來
mysqldump -u xxx -p database > database.sql –default-character-set=latin1
這時候 dump 出來的 database.sql 是 latin1 的檔案格式，然而是 utf8 的文字資料，因此這時若用 vi 開檔看它 …
還是會發現中文字是鬼畫符 … 這時不要被眼前的假象所騙 … 以為自己做錯 .. 幾接著做下一步驟。

b. 把 dump 出來的 database.sql 下載回去 .. 用 emEditor（網路上可免費下載，是個支援 utf-8 的編輯器）把 database.sql 打開，
它可以正常識別 utf8 的文字 ..用取代的功能，把 latin1 都取代成 utf8 ，之後直接另存新檔 .. 這時把檔案類型設定儲存成 UTF-8。

c. 再把 database-utf8.sql 上傳到 server .. 這時我們用 vi 開啟它 .. 就可以看見是正常的中文字 .. 這就表示檔案沒問題囉。

d. 把檔案匯入資料庫
mysql database < database-utf8.sql -u xxx -p --default-character-set=utf8

12 楼 liudaoru 2009-03-16  

MYSQL开发常用知识汇总
http://www.haoyiyong.com/idc/database/200903/04-215.html

11 楼 liudaoru 2009-03-16  

MySQL Explain命令用于查看执行效果
http://dev.21tx.com/2009/02/23/13069.html


提示：Explain命令用于查看执行效果。如果使用了extended，那么在执行完explain语句后，可以使用show warnings语句查询相应的优化信息。
MySQL的Explain命令用于查看执行效果。虽然这个命令只能搭配select类型语句使用，如果你想查看update，delete类型语句中的索引效果，也不是太难的事情，只要保持条件不变，把类型转换成select就行了。

explain的语法如下：

explain [extended] select ... from ... where ...

10 楼 liudaoru 2009-03-16  

explain select * from CHIP;

9 楼 liudaoru 2009-03-16  

MySQL查询优化器浅析
http://tech.ddvip.com/2008-01/120060814240651.html

8 楼 liudaoru 2009-03-16  

mysql中的trace工具
http://zhaizhenxing.blog.51cto.com/643480/134592

mysql从5.0.37后开始支持show profiles语法，用来对sql语句进行trace跟踪，下面是mysql help中对show profiles的解释：
mysql> ? show profiles;
Name: 'SHOW PROFILES'
Description:
Syntax:
SHOW PROFILES
SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT n [OFFSET n]]
type:
    ALL
  | BLOCK IO
  | CONTEXT SWITCHES
  | CPU
  | IPC
  | MEMORY
  | PAGE FAULTS
  | SOURCE
  | SWAPS
The SHOW PROFILES and SHOW PROFILE statements display profiling
information that indicates resource usage for statements executed
during the course of the current session.
Profiling is controlled by the profiling session variable, which has a
default value of 0 (OFF). Profiling is enabled by setting profiling to
1 or ON:
mysql> SET profiling = 1;
SHOW PROFILES displays a list of the most recent statements sent to the
master. The size of the list is controlled by the
profiling_history_size session variable, which has a default value of
15. The maximum value is 100. Setting the value to 0 has the practical
effect of disabling profiling.
All statements are profiled except SHOW PROFILES and SHOW PROFILE, so
you will find neither of those statements in the profile list.
Malformed statements are profiled. For example, SHOW PROFILING is an
illegal statement, and a syntax error occurs if you try to execute it,
but it will show up in the profiling list.
SHOW PROFILE displays detailed information about a single statement.
Without the FOR QUERY n clause, the output pertains to the most
recently executed statement. If FOR QUERY n is included, SHOW PROFILE
displays information for statement n. The values of n correspond to the
Query_ID values displayed by SHOW PROFILES.
The LIMIT n clause may be given to limit the output to n rows. If LIMIT
is given, OFFSET n may be added to begin the output n rows into the
full set of rows.
By default, SHOW PROFILE displays Status and Duration columns. The
Status values are like the State values displayed by SHOW PROCESSLIST,
althought there might be some minor differences in interpretion for the
two statements for some status values (see
http://dev.mysql.com/doc/refman/5.0/en/thread-information.html).
Optional type values may be specified to display specific additional
types of information:
o ALL displays all information
o BLOCK IO displays counts for block input and output operations
o CONTEXT SWITCHES displays counts for voluntary and involuntary
  context switches
o CPU displays user and system CPU usage times
o IPC displays counts for messages sent and received
o MEMORY is not currently implemented
o PAGE FAULTS displays counts for major and minor page faults
o SOURCE displays the names of functions from the source code, together
  with the name and line number of the file in which the function
  occurs
o SWAPS displays swap counts
Profiling is enabled per session. When a session ends, its profiling
information is lost.
URL: http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html
Examples:
mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)
mysql> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE T1 (id INT);
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW PROFILES;
+----------+----------+--------------------------+
| Query_ID | Duration | Query                    |
+----------+----------+--------------------------+
|        0 | 0.000088 | SET PROFILING = 1        |
|        1 | 0.000136 | DROP TABLE IF EXISTS t1  |
|        2 | 0.011947 | CREATE TABLE t1 (id INT) |
+----------+----------+--------------------------+
3 rows in set (0.00 sec)
mysql> SHOW PROFILE;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| checking permissions | 0.000040 |
| creating table       | 0.000056 |
| After create         | 0.011363 |
| query end            | 0.000375 |
| freeing items        | 0.000089 |
| logging slow query   | 0.000019 |
| cleaning up          | 0.000005 |
+----------------------+----------+
7 rows in set (0.00 sec)
mysql> SHOW PROFILE FOR QUERY 1;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| query end          | 0.000107 |
| freeing items      | 0.000008 |
| logging slow query | 0.000015 |
| cleaning up        | 0.000006 |
+--------------------+----------+
4 rows in set (0.00 sec)
mysql> SHOW PROFILE CPU FOR QUERY 2;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| checking permissions | 0.000040 | 0.000038 |   0.000002 |
| creating table       | 0.000056 | 0.000028 |   0.000028 |
| After create         | 0.011363 | 0.000217 |   0.001571 |
| query end            | 0.000375 | 0.000013 |   0.000028 |
| freeing items        | 0.000089 | 0.000010 |   0.000014 |
| logging slow query   | 0.000019 | 0.000009 |   0.000010 |
| cleaning up          | 0.000005 | 0.000003 |   0.000002 |
+----------------------+----------+----------+------------+
7 rows in set (0.00 sec)
例子中的使用方法已经写的很清楚了，以后有性能问题的sql可以用此方法来帮助找原因了！
本文出自 “帅小伙的博客” 博客，请务必保留此出处http://zhaizhenxing.blog.51cto.com/643480/134592

7 楼 liudaoru 2009-03-13  

使用sql语句查看表的创建结构：
SHOW CREATE TABLE t\G

6 楼 liudaoru 2009-03-13  

获取表结构：
mysqldump -d -u root r --tables QTIME >test.txt

获取库结构：
mysqldump -d -u root r >test.txt

去掉-d则是导出数据。

5 楼 liudaoru 2008-11-30  

查找mysql安装路径的方法：
1、用mysql.exe客户端登录后，查看：Show   variables   like   '%basedir%';
2、find / -name mysql -print；

参考：http://topic.csdn.net/t/20050919/17/4280025.html
http://www.lslnet.com/linux/dosc1/51/linux-345160.htm

4 楼 liudaoru 2008-11-04  

Proxool连接池

  开始使用 Proxool连接池，这东东真是好使，配置简单又好用，还有1个专门检测连接池的servlet,用它搞数据库连接，一个字：爽！

官方网址：http://proxool.sourceforge.net/

最新版本.0.8.3

可以根据自己的实际情况，选择不同的配置，可以选择properties、XML、Servlet等配置，官方网站上都有介绍，连文档都不需要看，直接拿来例子就可以使用，就象几年前开始使用php+mysql读取数据库时，有点象"一只手到数据库里抓数据"的感觉，简单实用又方便...

我使用的XML配置的数据库连接形式，自己写了一个class，用来做数据库连接

package shield.sysadm.database;

import java.sql.*;
import org.apache.log4j.*;

public class InitAction
{
  private Connection conn;
  Logger log  = Logger.getLogger("InitAction");

  public Connection getConnection()
  {
   try
   {
   conn = DriverManager.getConnection("proxool.xml-db");
   }
   catch(Exception ex)
   {
     log.error("数据库连接异常:"+ex.toString());
   }
   if(conn==null)
   {
    log.error("连接无效...");
   }
   return conn;
  }
 
}

而每次在使用时只需要:

Connection conn = new InitAction().getConnection();

即可，真是太方便了！

注:使用完之后需要一定得conn.close(),不然连接很快会被用光..那就不爽了！^_^

另外Proxool还自带了个检测其连接的servlet,将如下:

<servlet>
    <servlet-name>Admin</servlet-name>
    <servlet-class>org.logicalcobwebs.proxool.admin.servlet.AdminServlet</servlet-class>
  </servlet>
<servlet-mapping>
    <servlet-name>Admin</servlet-name>
    <url-pattern>/admin</url-pattern>
  </servlet-mapping>

放置到web.xml中去，然后在地址栏敲入地址，接下来你就可以查看到你的数据库连接情况了!

From: http://www.360doc.com/showWeb/0/0/10111.aspx

3 楼 liudaoru 2008-11-04  

MySQL数据库中用GRANT语句增添新用户

shell> mysql --user=root mysql

mysql> GRANT ALL PRIVILEGES ON *.* TO monty@localhost

IDENTIFIED BY 'something' WITH GRANT OPTION;

mysql> GRANT ALL PRIVILEGES ON *.* TO monty@"%"

IDENTIFIED BY 'something' WITH GRANT OPTION;

mysql> GRANT RELOAD,PROCESS ON *.* TO admin@localhost;

mysql> GRANT USAGE ON *.* TO dummy@localhost;

From: http://www.pcdog.com/edu/mysql/2007/04/z184417.html

2 楼 liudaoru 2008-11-04  

mysql 执行sql文件

mysql –h localhost –u root –p ******  databasename < ***.sql

From：http://blog.donews.com/litterboy/archive/2006/06/07/906445.aspx

1 楼 liudaoru 2008-10-30  

From: http://www.ccvita.com/206.html

REPLACE的运行与INSERT很相像。只有一点除外，如果表中的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值，则在新记录被插入之前，旧记录被删除。请参见13.2.4节，“INSERT语法”。