标签 mysql 文章存档

MySQL

一、查看 MySQL 设置的变量值：SHOW VARIABLES; 详情见 SHOW 语法：http://dev.mysql.com/doc/refman/5.5/en/show.html

二、windows 下忘记 root 密码的解决方法：
1、关闭 MySQL 服务
2、cmd 进入 mysqld 所在文件夹
3、cmd 下运行 mysqld –skip-grant-tables # 跳过授权表启动 MySQL 服务
4、打开新的 cmd 窗口
运行 mysql -uroot -p
提示输入密码继续回车 # 此时 root 的密码为空
use mysql # 选择 mysql 库
UPDATE user SET Password=PASSWORD(‘newpassword’) where USER=’root’; # 更改 root 密码
FLUSH PRIVILEGES; # 刷新权限，至此，完成

三、查看 MySQL-Server 的详细信息（加载配置文件的路径）
cmd 模式进入这个目录 \Program Files\MySQL\MySQL Server5.6\bin
运行：mysqld –verbose –help
在输出里面查找 “Default options are read from the following files in the given order” 就可以找到配置文件的路径。
或者，在计算机管理-服务里面找到 MySQL 的服务，查看服务属性，你就会看到加载的配置文件了。

四、开启慢查询

set global long_query_time=2;
set global slow_query_log=1;

五、mysql按编码计算字符长度而不是字节数， varchar(100) 表示容量为100个utf-8编码的字符个数，不是100个字节，插入超过容量长度的字符会被自动截断。如果varchar设定了utf8编码，varchar(2) => 两个汉字，比如[你好]。

六、As of MySQL 5.1.29, the –log-slow-queries option is deprecated and is removed (along with the log_slow_queries system variable) in MySQL 5.6. Instead, use the –slow_query_log option to enable the slow query log and the –slow_query_log_file=file_name option to set the slow query log file name.

七、In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.

八、重命名表语句：ALTER TABLE `旧表名` RENAME TO `新表名`;

九、今天出现错误：Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_c,IMPLICIT) for operation ‘=’。两个表在 JOIN 查询时，因为表的编码不一样，导致出现此错误，请将编码改为 utf8_general_ci 即可（2013-11-08 18:29:03）

Foreign key constraint is incorrectly formed

【朋友，如果你也遇到了这个问题，建立外键的字段必须和引用表的字段一模一样的类型，请用 show create table table_name 查看是否一致。】

今天在给一个表建外键的时候出现如题所示错误。

情况是这样的。我有一个bysj_student表，结构如下：

CREATE TABLE `bysj_student` (
	`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	`stuno` VARCHAR(20) NOT NULL,
	`name` VARCHAR(20) NOT NULL,
	`gender` VARCHAR(2) NOT NULL,
	`major_id` INT(11) UNSIGNED NOT NULL,
	`tel` VARCHAR(11) NOT NULL,
	`email` VARCHAR(50) NOT NULL,
	PRIMARY KEY (`id`)
)

还有另一个bysj_stu_sub表，结构如下：

CREATE TABLE `bysj_stu_sub` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`student_id` INT(11) NULL DEFAULT NULL,
	PRIMARY KEY (`id`),
	INDEX `student_id` (`student_id`),
	CONSTRAINT `bysj_stu_sub_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `bysj_student` (`id`)
)

bysj_stu_sub表里的student_id是bysj_student表的外键，但是在创建bysj_stu_sub表时，会提示错误：

SQL Error (1005): Can't create table 'test.bysj_stu_sub' (errno: 150)  Foreign key constraint is incorrectly formed

网上搜索之后才明白，建立外键的字段必须和引用表的字段一模一样的类型。
于是将bysj_stu_sub表的student_id字段属性改为

`student_id` INT(11) UNSIGNED NULL DEFAULT NULL

再建表就没有问题了。

（原博客发布时间：2011-11-27 09:27:26）

MySQL UNION 用法

有这样两张表 post 和 postmeta。

表 post 结构如下

CREATE TABLE `post` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) CHARACTER SET utf8 NOT NULL,
  `content` text CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`id`)
)

表 postmeta 结构如下

CREATE TABLE `postmeta` (
  `meta_id` int(11) NOT NULL AUTO_INCREMENT,
  `post_id` int(11) NOT NULL,
  `meta_key` text NOT NULL,
  `meta_value` text NOT NULL,
  PRIMARY KEY (`meta_id`)
)
INSERT INTO `postmeta` (`meta_id`, `post_id`, `meta_key`, `meta_value`) VALUES
(1, 1, 'count_views_all', '666'),
(2, 1, 'count_views_day-20130117', '44'),
(3, 1, 'count_views_week-201301', '343'),
(4, 2, 'count_views_all', '3453'),
(5, 3, 'count_views_all', '34553'),
(6, 4, 'count_views_all', '553'),
(7, 3, 'count_views_day-20130117', ''),
(8, 4, 'count_views_day-20130117', '35'),
(9, 5, 'count_views_day-20130111', '5'),
(10, 5, 'count_views_day-20130112', '12'),
(11, 5, 'count_views_day-20130113', '34'),
(12, 5, 'count_views_day-20130114', '45'),
(13, 5, 'count_views_day-20130115', '21'),
(14, 5, 'count_views_day-20130116', '23'),
(15, 5, 'count_views_day-20130117', '65');

想获取所有文章，并且按全部阅读量排序，理所当然，我写出了这样的查询语句：

SELECT `post`.*,`views_count` FROM `post`
JOIN
SELECT `meta_value` AS `views_count` WHERE `meta_key`='count_views_all' FROM `postmeta`
ON `post`.`id`=`postmeta`.`post_id`
ORDER BY `views_count` DESC

运行，提示 SQL 语法错误。查看 MySQL 官方文档，总结出 JOIN 两边必须是表（有错还希望看官您指出），因此，我加了一个子句。如下，OK。

SELECT `post`.*,`views_count` FROM `post`
JOIN
(SELECT `post_id`,`meta_value` AS `views_count` FROM `postmeta` WHERE `meta_key`='count_views_all') AS `meta`
ON `post`.`id`=`meta`.`post_id`
ORDER BY (`views_count`+0) DESC

Linux 下利用 mdbtools 将 access 数据库导入到 MySQL 数据库

我的系统是：Ubuntu 12.10

access 数据库名为：old_database.mdb

MySQL 数据库名为：new_database

MySQL 用户名为：USER

MySQL 密码为：PASS

1、首先安装 mdbtools，mdbtools 包括：mdb-array mdb-header mdb-parsecsv mdb-schema mdb-tables mdb-export mdb-hexdump mdb-prop mdb-sql mdb-ver 等工具

sudo apt-get install mdbtools

2、导入表结构

mdb-schema old_database.mdb mysql | mysql -uUSER -pPASS new_database

3、查看 access 数据库中的表

mdb-tables old_database.mdb

3、导入表数据。多次执行此命令，每次导入一个表，假设当次导入的表名为 table_name

mdb-export -I mysql -D '%Y-%m-%d %H:%M:%S' old_database.mdb table_name | mysql -uUSER -pPASS new_database