非常感谢wwwwb的指导,原因如下:mysql> SELECT * FROM information_schema.KEY_COLUMN_USAGE where table_schema='be' and constraint_name like '%item_general%'; +--------------------+-------------------+----------------------------------+---------------+--------------+------------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+ | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | POSITION_IN_UNIQUE_CONSTRAINT | REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME | +--------------------+-------------------+----------------------------------+---------------+--------------+------------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+ | def | be | FK_RINVOICE_DETAILS_ITEM_GENERAL | def | be | rinvoice_details | item_id | 1 | 1 | be | item_general | item_id | +--------------------+-------------------+----------------------------------+---------------+--------------+------------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+mysql> create database test; Query OK, 1 row affected (0.00 sec)mysql> use test; Database changed mysql> CREATE TABLE `a` -> ( -> `B_ID` VARCHAR(32) -> ); Query OK, 0 rows affected (0.01 sec)mysql> ALTER TABLE `a` ADD CONSTRAINT FK_A_B FOREIGN key (`b_id`) REFERENCES `b`(`id`); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0mysql> CREATE TABLE `b` -> ( -> `ID` VARCHAR(32) -> ); ERROR 1215 (HY000): Cannot add foreign key constraint 话说,早上来到之后登录mysql之后,运行个名为xxxyyy的脚本。提示丢失连接。得知原因是服务神奇般的死掉了。 好吧,开启就是。service.msc(当前的系统为Windows7+Mysql 5.6)。被域控制! 之后傻乎乎的Win+R cd MYSQL_HOME/bin mysqld -install 出错,安装被拒绝。 无奈,找了个管理员权限。 C:/windows/System32/cms.exe--> 以管理员权限运行。 cd MYSQL_HOME/bin mysqld -install net start mysql 之后悲剧出现了!!!! mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec) 我的数据库实例不见了....好吧,日子还是要过的。风水不是太好,重新开个CMD窗口。 mysql -u root -p ****** 我被localhost拒绝了密码重新输入一遍,一样的结果。 光脚的不怕穿鞋的。 mysql -u root 顺利进去了....再看,my.ini # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the # *** default location during install, and will be replaced if you # *** upgrade to a newer version of MySQL.[mysqld]# Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M# Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin# These are commonly set, remove the # and set as required. # basedir = ..... # datadir = ..... # port = ..... # server_id = ..... # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 以上,求扫盲,求科普。
basedir datadir 设置为你的工作目录试试
设置之后,没有什么改变。不过无所谓了,这是开发用的PC,而且还有数据备份。 只是有些好奇: 刚刚使用 mysql -u root -p xxxxxx 登录进去,mysql怎么就丢失连接了呢?(什么原因会造成mysql连接丢失) 以管理员身份安装服务后,为何丢失数据?(安装mysql服务意味着什么?应该不单单是开启一个端口号那么简单吧。)
+--------------------+-------------------+----------------------------------+---------------+--------------+------------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | POSITION_IN_UNIQUE_CONSTRAINT | REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+--------------------+-------------------+----------------------------------+---------------+--------------+------------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+
| def | be | FK_RINVOICE_DETAILS_ITEM_GENERAL | def | be | rinvoice_details | item_id | 1 | 1 | be | item_general | item_id |
+--------------------+-------------------+----------------------------------+---------------+--------------+------------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+mysql> create database test;
Query OK, 1 row affected (0.00 sec)mysql> use test;
Database changed
mysql> CREATE TABLE `a`
-> (
-> `B_ID` VARCHAR(32)
-> );
Query OK, 0 rows affected (0.01 sec)mysql> ALTER TABLE `a` ADD CONSTRAINT FK_A_B FOREIGN key (`b_id`) REFERENCES `b`(`id`);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> CREATE TABLE `b`
-> (
-> `ID` VARCHAR(32)
-> );
ERROR 1215 (HY000): Cannot add foreign key constraint
话说,早上来到之后登录mysql之后,运行个名为xxxyyy的脚本。提示丢失连接。得知原因是服务神奇般的死掉了。
好吧,开启就是。service.msc(当前的系统为Windows7+Mysql 5.6)。被域控制!
之后傻乎乎的Win+R
cd MYSQL_HOME/bin
mysqld -install
出错,安装被拒绝。
无奈,找了个管理员权限。
C:/windows/System32/cms.exe--> 以管理员权限运行。
cd MYSQL_HOME/bin
mysqld -install
net start mysql
之后悲剧出现了!!!!
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
我的数据库实例不见了....好吧,日子还是要过的。风水不是太好,重新开个CMD窗口。
mysql -u root -p
******
我被localhost拒绝了密码重新输入一遍,一样的结果。
光脚的不怕穿鞋的。
mysql -u root
顺利进去了....再看,my.ini
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.[mysqld]# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 以上,求扫盲,求科普。
datadir
设置为你的工作目录试试
只是有些好奇:
刚刚使用 mysql -u root -p
xxxxxx
登录进去,mysql怎么就丢失连接了呢?(什么原因会造成mysql连接丢失)
以管理员身份安装服务后,为何丢失数据?(安装mysql服务意味着什么?应该不单单是开启一个端口号那么简单吧。)
论坛这么多讲物理数据库优化的,有一点想法:
B+树是DB2、Oracle、SQL Server支持的基本索引方法,几乎占据了所有系统场景。
(摘自《物理数据库设计 -- 索引、视图和存储技术》)
能不能基于B+树将典型的业务场景(Mysql为例)汇总下。以便讨论和查阅。这个重复率太高了。