mysql 专项

[itang]

MySQL 架构

• [ ] http://www.innomysql.net/article/11513.html

  学习

  Identifying and Non-Identifying Relationships

An identifying relationship means that the child table cannot be uniquely identified without the parent. For example, you have this situation in the intersection table used to resolve a many-to-many relationship where the intersecting table's Primary Key is a composite of the left and right (parents) table's Primary Keys.

A non-identifying relationship is one where the child can be identified independently of the parent ( Account - AccountType)

mysql 插入优化

• [ ] http://fenglinyuye.lofter.com/post/1b494e_60f0e2

  修改配置

ysql根据配置文件会限制server接受的数据包大小。 有时候大的插入和更新会被max_allowed_packet 参数限制掉，导致失败。

http://smilemonkey.iteye.com/blog/660889

1） 方法1
可以编辑my.cnf来修改（windows下my.ini）,在[mysqld]段或者mysql的server配置段进行修改。
max_allowed_packet = 20M
如果找不到my.cnf可以通过
mysql --help | grep my.cnf
去寻找my.cnf文件。
2） 方法2
（很妥协，很纠结的办法）
进入mysql server
在mysql 命令行中运行
set global max_allowed_packet = 2*1024*1024*10
然后关闭掉这此mysql server链接，再进入。
show VARIABLES like '%max_allowed_packet%';
查看下max_allowed_packet是否编辑成功

通过ip访问

sudo gedit /etc/mysql/my.cnf

bind-address = 127.0.0.1

http://www.cnblogs.com/cnblogsfans/archive/2009/09/21/1570942.html

命令

查看MySQL Server 版本

mysqld --version
141028 22:09:16 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
mysqld  Ver 5.5.40-0ubuntu0.14.04.1 for debian-linux-gnu on x86_64 ((Ubuntu))

Mysql初始化root密码和允许远程访问:

http://www.cnblogs.com/live41/archive/2013/04/02/2995178.html

重启

sudo /etc/init.d/mysql restart

SQL

查询数据库所有表

SELECT * FROM INFORMATION_SCHEMA.TABLES where table_schema = 'mydb'

查询表所有字段

"SELECT column_name,column_comment FROM information_schema.columns WHERE table_name = ? "

表更新 by关联

update creative c set click_url = (select click_url from adgroup where id = c.adgroup_id);

数据导出导入

mysqldump --opt -h127.0.0.1 -uroot -proot --skip-lock-tables mydb>database.sql

导入数据库 source mydb.sql mysql -u用户名 -p 数据库名 < 数据库名.sql mysql -uroot -p mydb < database.sql