日志分类:SQL

MySQL模式:Strict Mode

2013 年 04 月 19 日 上午 11:43  |  分类:SQL

关闭MySQL的strict mode的具体做法:找到MySQL目录下的my.ini,将其中的sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION” 修改为sql-mode=”NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”,重新启动 Mysql服务

一. Strict Mode的描述:
根据 mysql5.0以上版本 strict mode (STRICT_TRANS_TABLES) 的限制:
1).不支持对not null字段插入null值
2).不支持对自增长字段插入”值,可插入null值
3).不支持 text 字段有默认值
eg:
1.看下面代码:(第一个字段为自增字段)
$query=”insert into demo values(”,’$firstname’,’$lastname’,’$sex’)”;
上边代码只在非strict模式有效,如果是strict模式下就会出错。

2.$query=”insert into demo values(NULL,’$firstname’,’$lastname’,’$sex’)”;
上边代码只在strict模式有效。把空值”换成了NULL.

二. 让数据库支持Strict Mode:
1.对数据库结构进行以下改进来支持strict mode:
1) 给所有not null字段都设置非null默认值,字符串默认值为 ”,数值默认值为 0,日期默认值为 ‘0000-00-00 00:00:00’
2) 去掉text字段的默认值
3) 规范化改进: 把 title 字段统一改为 varchar(255),把有默认值的null字段改为not null字段

2.如果安装的PHP程序数据库结构关闭Strict mode
1).一个是安装mysql5.0(含以上)版本的时候去掉strict mode。
编辑 my.ini,关闭Strict Mode:
sql-mode=”NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”

2). 另一个就是修改查询语句。例如在
if ($this->dbcharset) {
@mysql_query(“SET NAMES “.$this->dbcharset);
}
后面执行
mysql_query(“SET @@sql_mode = ””);

注意确定你使用的是MySQL5 以上版本

mysqli方式类似,就是执行的是
mysqli_query($this->connection_id, “SET @@sql_mode = ””);

全文阅读 »

MySQL5.0中文-各种命令

2012 年 01 月 17 日 下午 1:20  |  分类:SQL

将.sql大文件导入到mysql中及常用命令
用命令
mysql -u root -p book -u root -p
建表时用命令:create table name(name varchar(20))[TYPE=MyISAM,]default character set
gbk;

=====================
连接服务者:
mysql -h host -u user -p

=====================
新增超级用户:
GRANT ALL PRIVILEGES ON *.* TO grb@”%” IDENTIFIED BY ‘password’ WITH GRANT OPTION;
全文阅读 »

MySQL在命令行下导入导出.sql文件的方法

2011 年 08 月 22 日 上午 8:34  |  分类:SQL,Zen-Cart二次开发

MySQL导入导出.sql文件步骤如下:

一.MySQL的命令行模式的设置:

桌面->我的电脑->属性->环境变量->新建->

PATH=“;path\mysql\bin;”其中path为MySQL的安装路径。

二.简单的介绍一下命令行进入MySQL的方法:

1.C:\>mysql -h hostname -u username -p

按ENTER键,等待然后输入密码。这里hostname为服务器的名称,如localhost,username为MYSQL的用户名,如root。

进入命令行后可以直接操作MySQL了。

2.简单介绍一下MySQL命令:

mysql->CREATE DATABASE dbname;//创建数据库

mysql->CREATE TABLE tablename;//创建表

mysql->SHOW DATABASES;//显示数据库信息,有那些可用的数据库。

mysql->USE dbname;//选择数据库

mysql->SHOW TABLES;//显示表信息,有那些可用的表

mysql->DESCRIBE tablename;//显示创建的表的信息

三.从数据库导出数据库文件:

1.将数据库mydb导出到e:\mysql\mydb.sql文件中:

打开开始->运行->输入cmd 进入命令行模式

c:\>mysqldump -h localhost -u root -p mydb >e:\mysql\mydb.sql

然后输入密码,等待一会导出就成功了,可以到目标文件中检查是否成功。

2.将数据库mydb中的mytable导出到e:\mysql\mytable.sql文件中:

c:\>mysqldump -h localhost -u root -p mydb mytable>e:\mysql\mytable.sql

3.将数据库mydb的结构导出到e:\mysql\mydb_stru.sql文件中:

c:\>mysqldump -h localhost -u root -p mydb –add-drop-table >e:\mysql\mydb_stru.sql

//-h localhost可以省略,其一般在虚拟主机上用

四.从外部文件导入数据到数据库中:

从e:\mysql\mydb2.sql中将文件中的SQL语句导入数据库中:

1.从命令行进入mysql,然后用命令CREATE DATABASE mydb2;创建数据库mydb2。

2.退出mysql 可以输入命令exit;或者quit;

3.在CMD中输入下列命令:

c:\>mysql -h localhost -u root -p mydb2 < e:\mysql\mydb2.sql 然后输入密码,就OK了。 五.下面谈一下关于导入文件大小限制问题的解决: 默认情况下:MySQL 对导入文件大小有限制的,最大为2M,所以当文件很大时候,直接无法导入,下面就这个问题的解决列举如下: 1.在php.ini中修改相关参数: 影响mysql导入文件大小的参数有三个: memory_limit=128M,upload_max_filesize=2M,post_max_size=8M 修改upload_max_filesize=200 M 这里修改满足你需要的大小,可以同时修改其他两项memory_limit=250M post_max_size=200M 这样就可以导入200M以下的.sql文件了。 上文中是把MySQL放置在系统路径中,其实不妨也行。例如我的MySQL安装目录为D:\MySQL Server 5.0; 则首先用cmd打开dos窗口,然后输入D:(没有'\')回车 此时应该会出现D:\>这样的标记,然后在其后面输入D:\MySQL Server 5.0\bin回车(顺序颠倒也行的)

出现D:\MySQL Server 5.0\bin> 接着输入mysqldump -u用户名 -p 数据库名 > 数据库名.sql(也可以输入路径);具体用法参照上文。

导入文件同样,只是改了‘>’为‘<’就行了。或者直接用source 也行: 常用source 命令 进入mysql数据库控制台 如mysql -u root -p(没必要,省略) mysql>use 数据库

然后使用source命令,后面参数为脚本文件(如这里用到的.sql)

mysql>source d:wcnc_db.sql

zen cart删除产品数据,订单和客户数据

2011 年 04 月 08 日 上午 11:45  |  分类:SQL,Zen-Cart二次开发

TRUNCATE TABLE products;
TRUNCATE TABLE products_description;
TRUNCATE TABLE products_to_categories;
TRUNCATE TABLE products_attributes;
TRUNCATE TABLE products_attributes_download;
TRUNCATE TABLE products_discount_quantity;
TRUNCATE TABLE products_options;
TRUNCATE TABLE products_options_types;
TRUNCATE TABLE products_options_values;
TRUNCATE TABLE products_options_values_to_products_options;

SELECT `categories_id` FROM `zen_categories` WHERE `parent_id`=3

SELECT * FROM `zen_products_to_categories` WHERE `categories_id` in (SELECT `categories_id` FROM `zen_categories` WHERE `parent_id`=3)

SELECT `products_id` FROM `zen_products_to_categories` WHERE `categories_id` in (SELECT `categories_id` FROM `zen_categories` WHERE `parent_id`=3)

DELETE FROM `products` WHERE `products_id` in (SELECT `products_id` FROM `zen_products_to_categories` WHERE `categories_id` in (SELECT `categories_id` FROM `categories` WHERE `parent_id`=1247));

DELETE FROM `products_description` WHERE `products_id` in (SELECT `products_id` FROM `zen_products_to_categories` WHERE `categories_id` in (SELECT `categories_id` FROM `zen_categories` WHERE `parent_id`=1247));

DELETE FROM `products_to_categories` WHERE `categories_id` in (SELECT `categories_id` FROM `zen_categories` WHERE `parent_id`=1247);

//删除订单和客户数据
TRUNCATE TABLE address_book;
TRUNCATE TABLE customers;
TRUNCATE TABLE customers_basket;
TRUNCATE TABLE customers_basket_attributes;
TRUNCATE TABLE customers_info;
TRUNCATE TABLE customers_wishlist;
TRUNCATE TABLE orders;
TRUNCATE TABLE orders_products;
TRUNCATE TABLE orders_products_attributes;
TRUNCATE TABLE orders_products_download;
TRUNCATE TABLE orders_status_history;
TRUNCATE TABLE orders_total;

//修改目录产品价格
UPDATE `zen_products` SET `products_price`=’35’,`products_price_retail`=’45’,`products_price_sample`=’30’ WHERE `products_id` in (SELECT `products_id` FROM `zen_products_to_categories` WHERE `categories_id` in (SELECT `categories_id` FROM `zen_categories` WHERE `parent_id`=4322));

UPDATE `zen_products` SET `products_price` = `products_price_sample` WHERE `products_id` in (SELECT `products_id` FROM `zen_products_to_categories` WHERE `categories_id` in (SELECT `categories_id` FROM `zen_categories` WHERE `parent_id`=2148));

UPDATE `zen_products` SET `products_price` = `products_price_sample` WHERE `products_id` in (SELECT `products_id` FROM `zen_products_to_categories` WHERE `categories_id` in (SELECT `categories_id` FROM `zen_categories` WHERE `parent_id`=2148));

SELECT *
FROM `zen_products`
WHERE `products_id`
IN (

SELECT `products_id`
FROM `zen_products_to_categories`
WHERE `categories_id`
IN (

SELECT `categories_id`
FROM `zen_categories`
WHERE `parent_id` =4382
)
)

OPTIMIZE TABLE的作用

2011 年 02 月 08 日 上午 10:53  |  分类:SQL

当您的库中删除了大量的数据后,您可能会发现数据文件尺寸并没有减小。这是因为删 除操作后在数据文件中留下碎片所致。Discuz! 在系统数设置界面提供了数据表优化的功能,可以去除删除操作后留下的数据文件碎片,减小文件尺寸,加快未来的读写操作。您只要在做完批量删除,或定期(如 每一两个月)进行一次数据表优化操作即可。

OPTIMIZE TABLE通过制作原来的表的一个临时副本来工作

OPTIMIZE TABLE语法

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] …
如果您已经删除了表的一大部分,或者如果您已经对含有可变长度行的表(含有 VARCHAR, BLOB或TEXT列的表)进行了很多更改,则应使用OPTIMIZE TABLE。被删除的记录被保持在链接清单中,后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新利用未使用的空间,并整理数据文件的碎片。

在多数的设置中,您根本不需要运行OPTIMIZE TABLE。即使您对可变长度的行进行了大量的更新,您也不需要经常运行,每周一次或每月一次即可,只对特定的表运行。

OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。

对于MyISAM表,OPTIMIZE TABLE按如下方式操作:

1. 如果表已经删除或分解了行,则修复表。

2. 如果未对索引页进行分类,则进行分类。

3. 如果表的统计数据没有更新(并且通过对索引进行分类不能实现修复),则进行更新。

对于BDB表,OPTIMIZE TABLE目前被映射到ANALYZE TABLE上。对于InnoDB表,OPTIMIZE TABLE被映射到ALTER TABLE上,这会重建表。重建操作能更新索引统计数据并释放成簇索引中的未使用的空间。请参见“ANALYZE TABLE语法”。

使用—skip-new或—safe-mode选项可以启动mysqld。通过启动mysqld,您可以使OPTIMIZE TABLE对其它表类型起作用。

注意,在OPTIMIZE TABLE运行过程中,MySQL会锁定表。

OPTIMIZE TABLE语句被写入到二进制日志中,除非使用了自选的NO_WRITE_TO_BINLOG关键词(或其别名LOCAL)。已经这么做了,因此,用于 MySQL服务器的OPTIMIZE TABLE命令的作用相当于一个复制主服务器,在默认情况下,这些命令将被复制到复制从属服务器中。

输出mysql数组

2010 年 12 月 08 日 上午 11:12  |  分类:SQL

mysql数组问题:
 //$qrystr = mysql_query(“SELECT zid,zname from `dede_onlinetest` order by zid asc;”);
 
 //形成新的数组
// while ($row = mysql_fetch_array($qrystr)) {
// $row1[]=$row;
// }

print_r($row1);

SQL中字符串替换方法

2010 年 10 月 06 日 下午 3:00  |  分类:SQL

REPLACE ( ‘string_expression1’ , ‘string_expression2’ , ‘string_expression3’ )
参数
‘ string_expression1 ‘
要搜索的字符串表达式。string_expression1 参数的数据类型可以是可隐式转换为 nvarchar 或 ntext 的数据类型。
‘ string_expression2 ‘
尝试查找的字符串表达式。string_expression2 参数的数据类型可以是可隐式转换为 nvarchar 或 ntext 的数据类型。
‘ string_expression3 ‘
替换字符串表达式。string_expression3 参数的数据类型可以是可隐式转换为 nvarchar 或 ntext 的数据类型。

返回值
nvarchar 或 ntext
如果任意参数为 NULL,则返回 NULL。
实际操作:
单变量用法:
declare @dim_time nvarchar(300)
set @dim_time=’ (CallStartTime>=”2009-01-01 23:00:00”) ‘
select @dim_time=replace(@dim_time,’CallStartTime’,’HoTime’);
print(@dim_time);
结果:(HoTime>=’2009-01-01 23:00:00′)

对结果集/表使用:
update table[表名] set Fields[字段名]=replace(Fields[字段名],’被替换原内容’,’要替换成的内容’)
例:update ProgInfo set Time=replace(Time,’2008-3-2′,’2007-03-02′)
eg:update meta_tags_products_description set metatags_description=replace(metatags_description,’favoruggs’,’abc’);

sql外键约束

2010 年 08 月 07 日 下午 4:21  |  分类:SQL

NO ACTION
指定如果试图删除/修改某一行,而该行的键被其他表的现有行中的外键所引用,则产生错误并回滚 DELETE/UPDATE语句。
CASCADE、SET NULL 和 SET DEFAULT
允许通过删除或更新键值来影响指定具有外键关系的表,这些外键关系可追溯到在其中进行修改的表。如果为目标表也定义了级联引用操作,那么指定的级联操作也将应用于删除或更新的那些行。不能为具有 timestamp 列的外键或主键指定 CASCADE。
ON DELETE CASCADE
指定如果试图删除某一行,而该行的键被其他表的现有行中的外键所引用,则也将删除所有包含那些外键的行。
ON UPDATE CASCADE
指定如果试图更新某一行中的键值,而该行的键值被其他表的现有行中的外键所引用,则组成外键的所有值也将更新到为该键指定的新值。 (如果 timestamp 列是外键或被引用键的一部分,则不能指定 CASCADE。 )
ON DELETE SET NULL
指定如果试图删除某一行,而该行的键被其他表的现有行中的外键所引用,则组成被引用行中的外键的所有值将被设置为 NULL。目标表的所有外键列必须可为空值,此约束才可执行。
ON UPDATE SET NULL
指定如果试图更新某一行,而该行的键被其他表的现有行中的外键所引用,则组成被引用行中的外键的所有值将被设置为 NULL。目标表的所有外键列必须可为空值,此约束才可执行。
ON DELETE SET DEFAULT
指 定如果试图删除某一行,而该行的键被其他表的现有行中的外键所引用,则组成被引用行中的外键的所有值将被设置为它们的默认值。目标表的所有外键列必须具 有默认值定义,此约束才可执行。如果某个列可为空值,并且未设置显式的默认值,则会使用 NULL 作为该列的隐式默认值。因 ON DELETE SET DEFAULT 而设置的任何非空值在主表中必须有对应的值,才能维护外键约束的有效性。
ON UPDATE SET DEFAULT
指 定如果试图更新某一行,而该行的键被其他表的现有行中的外键所引用,则组成被引用行中的外键的所有值将被设置为它们的默认值。目标表的所有外键列必须具 有默认值定义,此约束才可执行。如果某个列可为空值,并且未设置显式的默认值,则会使用 NULL 作为该列的隐式默认值。因 ON UPDATE SET DEFAULT 而设置的任何非空值在主表中必须有对应的值,才能维护外键约束的有效性。

ps:对于有外键的表,外键必须设置为允许空(null)才可以插入数据。

Sql语句设置mysql外键

2010 年 08 月 02 日 下午 8:03  |  分类:SQL

ALTER TABLE user ADD CONSTRAINT roleId
FOREIGN KEY (RoleId)
REFERENCES Role (roleid);

第一行是说要为user表设置外键,给这个外键起一个名字叫做roleId;第二行是说将本表的RoleId字段设置为外键;第三行是说这个外键受到的约束来自于Role表的roleid字段。

TRUNCATE与DELETE

2010 年 06 月 03 日 上午 7:45  |  分类:SQL

TRUNCATE   TABLE   在功能上与不带   WHERE   子句的   DELETE   语句相同:二者均删除表中的全部行。但   TRUNCATE   TABLE   比   DELETE   速度快,且使用的系统和事务日志资源少。  

DELETE   语句每次删除一行,并在事务日志中为所删除的每行记录一项。TRUNCATE   TABLE   通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放。

TRUNCATE   TABLE   删除表中的所有行,但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值,请改用   DELETE。如果要删除表定义及其数据,请使用   DROP   TABLE   语句。

对于由   FOREIGN   KEY   约束引用的表,不能使用   TRUNCATE   TABLE,而应使用不带   WHERE   子句的   DELETE   语句。由于   TRUNCATE   TABLE   不记录在日志中,所以它不能激活触发器。  

TRUNCATE   TABLE   不能用于参与了索引视图的表。