mysql 数据库字符集设置_mysql数据库字符集设置

1、 mysql UTF8设置

1)vi /etc/my.cnf

[client]

port = 3306

socket = mysql

default-character-set=utf8

[mysqld]

port = 3306

socket = mysql

skip-locking

init_connect='SET collation_connection = utf8_general_ci'

init_connect='SET NAMES utf8'

default-character-set=utf8

character-set-server = utf8

collation-server = utf8_general_ci

[mysql]

default-character-set=utf8

2) 重启mysql

3) 确认设置是否成功

show variables like 'character%';

+--------------------------+----------------------------+

| Variable_name | Value |

+--------------------------+----------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

2 mysqldump 导入导出

数据导出:mysqldump --default-character-set=gbk db > db.sql

数据导入:mysql --default-character-set=gbk db < db.sql

执行mysqldump sql:mysqldump -w "fmodify_date > '2010-4-10'"

执行指令后导出txt：mysql -uroot -p123 db_fund -e "select * from t_fund_bind limit 0,10" > bind.sql

修改字符集：

alter database da_name default character set 'gbk'

set names 'gbk'

set names gbk告诉mysql,接下来的数据将以该编码方式传输，等同于：

SET character_set_client = gbk;

SET character_set_results = gbk;

SET character_set_connection = gbk;

3  查看数据库状态

通过status指令查看当前的编码：

mysql> status;

Server characterset:    latin1

Db     characterset:    gbk

Client characterset:    gbk

Conn. characterset:    gbk

7)导入我们转换成gbk后的文件

mysql> source base_user_gbk.sql;

用户命令：

mysql> CREATE USER yy IDENTIFIED BY '123';

yy表示你要建立的用户名，后面的123表示密码

上面建立的用户可以在任何地方登陆。

如果要限制在固定地址登陆，比如localhost 登陆：

mysql> CREATE USER yy@localhost IDENTIFIED BY '123';

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

grant select,insert,update,delete on *.* to test1@"%" Identified by "abc";

格式：grant select on 数据库.* to 用户名@登录主机 identified by "密码"

修改密码：

mysql> grant   all   privileges   on   pureftpd.*   to   koko@localhost   identified   by   'mimi';

flush:

mysql> flush privileges;

查看用户信息：

mysql> select host,user from mysql.user;

sql inject:

1.使用PrepareStatement+bind-variable.

2.使用程序转换特殊字符,如mysql c api: mysql_real_escape_string()

3.自行编写函数进行校验

tomcat encode:http://wiki.apache.org/tomcat/FAQ/CharacterEncoding

jetty encode:http://docs.codehaus.org/display/JETTY/International+Characters+and+Character+Encodings

date加减语法

date + INTERVAL expr unit

select current_date() - INTERVAL 1 DAY;

where fdate = current_date()- INTERVAL 2 DAY

mysql insert or update的替代做法

insert t_app_pv (f_app_id,f_pv) values (?,?) on duplicate key update f_pv=?

insert select 语法

SELECT * FROM t_cft_fund_log t order by f_operation_date limit 0,10 ;

SELECT * FROM t_cft_fund_log_history  order by f_operation_date desc limit 0,10;

insert into t_cft_fund_log_history select * from t_cft_fund_log  where f_operation_date < '2010-10-01';INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [(col_name,...)]

SELECT ...

[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

With INSERT ... SELECT, you can quickly insert many rows into a table from one or many tables. For example:INSERT INTO tbl_temp2 (fld_id)

SELECT tbl_temp1.fld_order_id

FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;