mysql 日常总结

1 . 解决MySQL不允许从远程访问的方法

    grant all on yourdb.* to yourUsername@yourHost identified by "yourPassword";
    flush privileges;

2.mysql参数化查询

2.1命令行SQL code:

set @n = 156027;
select * from tab_name where id=@n;

如果是在存储过程里了，也可以这样
SQL code:
declare n int;
set n = 156027;
select * from tab_name where id=n;

2.2通过select ... into... 赋值:

select    username   form    users   where   id=1    into    @result;
select    @result;

2.3php参数化查询mysql:

$query = sprintf("SELECT * FROM Users where UserName='%s' and Password='%s'",
mysql_real_escape_string($Username),
mysql_real_escape_string($Password));……

此时如果提交username=user,  password=test'or'1'='1,如果不用参数化查询会变成:
SELECT * FROM users where username='test' and   password='test'or'1'='1'

如果使用参数化查询,则查询语句会变成:
SELECT * FROM users where username='test' and password='test\'or\'1\'=\'1'   //使用\' 来转义了', 从而保证了安全提交.