bitsCN.com REPAIR TABLE `table_name` 修复表 OPTIMIZE TABLE `table_name` 优化表 REPAIR TABLE 用于修复被破坏的表。 OPTIMIZE TABLE 用于回收闲置的数据库空间，当表上的数据行被删除时，所占据的磁盘空间并没有立即被回收，使用了OPTIMIZE TABLE命令后这些空间将被回收，并且对磁盘上的数据行进行重排（注意：是磁盘上，而非数据库）。 多数时间并不需要运行OPTIMIZE TABLE，只需在批量删除数据行之后，或定期（每周一次或每月...
bitsCN.com 今天在写 mysql 遇到一个比较特殊的问题。 mysql 语句如下: update wms_cabinet_form set cabf_enabled=0 where cabf_id in ( SELECT wms_cabinet_form.cabf_id FROM wms_cabinet_form Inner Join wms_cabinet ON wms_cabinet_form.cabf_cab_id = wms_cabinet.cab_id Inner Join wms_cabinet_row ON wms_cabinet.cab_row_id =wms_cabinet_row.row_id where wms_cabinet_row.row_site_id=27 and wms_cabinet_form.cabf_e...
show tables; --显示该数据库里的所有表show columns from 表名; --显示表字段use information_schemaselect * from columns where table_name='表名'; --显示表字段
众所周知drop table会严重的消耗服务器IO性能，如果被drop的table容量较大，甚至会影响到线上的正常。首先，我们看一下为什么drop容量大的table会影响线上服务 直接执行drop table，mysql会将表定义和表数据全都删除，包括磁盘上的物理文件，也包括buffer pool中的内存数据。 这就分两步，第一步从buffer pool中删除，这会涉及到table_cache的lock，如果持有table_cache的lock，这将导致其他查询都无法执行。这种情况在没有i...
1，Mysqldump的时候报错如下：2014-05-05 14:12:37 7f004a9a2700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.但是show tables我看这个表示存在的：但是show create table innodb_index_stats;报错如下：mysql>show create table innodb_index_stats;ERROR 1146 (42S02): Tablemysql.innodb_index_stats doesnt existmysql>2，估计是表坏了，去数据库服务器上面的mysql库的目录看下这些表的数据文件： 3，看到表...
网站的 MYSQL 数据库出现错误如下提示：Table ‘./dede_archives' is marked as crashed and last (automatic?)该问题即为相关表受到损坏，一般情况下，可以用 phpmyadmin 对该表进行修复。如果是独立的服务器，也可使用命令行方式进行修复。修复前将mysql服务停止：/etc/init.d/mysqld stop然后执行如下命令(参考)修复该表：myisamchk -r /usr/local/mysql/var/dede/dede_archives.MYI注意数据库表所在路径。 win 主机下可类比执行...
1. 什么是table_idMySQL binlog文件按格式分为文件头部和事件信息。文件头部占4字节，内容固定为："/xfe/x62/x69/x6e"，接下来就是各个event了。event有多种类型，比如ROTATE_EVENT对应的记录了binlog切换到下一个binlog文件的信息，XID_EVENT记录了一个事务提交的相关信息。binlog_format可以设置为statement和row的方式。当设置为statement情况下，DML会记录为原始的SQL，也就是记录在QUERY_EVENT中。而row会记录为TABLE_MAP_EVE...
Some of us Perconians are atOpenStack summitthis week in Atlanta.Matt Griffin, our director of product management,tweetedabout the turbo-hipster CI talk about their experience of ALTER TABLEs running faster on Percona Server. Oracle’s Morgan Tockerthentweeted in response, asking why this was the case. I decided that the simplest way to answer that was here in this post.The reason for this is the ...
MySQL supports Temporary tables to store theresultsetstemporarily for a given connection. Temporary tables are created with the keyword TEMPORARY along with the CREATE TABLE statement.Let us create the temporary table named TempCREATE TEMPORARY TABLE TEMP (id INT); Now you can find out the column names using DESC commandDESC TEMP;The above returns the following resultThis table can be accessed onl...
In what is probably a surprise to many developers, MySQL currently stores its critical system tables in MyISAM instead of InnoDB. Since MyISAM isn’t ACID compliant, there is a chance for data loss or corruption when modifying system objects such as privilege. Morgan Tocker has announced thatthe MySQL team intends to use InnoDB for system tables.MyISAMis the original storage engine for MySQL. It i...
In the MySQL team, we are changing the system tables currently located in themysqlschema from MyISAM to InnoDB.Looking at this historically:MyISAM was the default storage engine up until MySQL 5.5.In 5.5 almost 4 years ago, the default storage engine changed to InnoDB, however system tables used for features such as storing privileges and timezones remained as MyISAM.Unlike MyISAM, InnoDB is an AC...
There is one new feature in MySQL 5.6 that didn’t get the attention it deserved (at least from me) : “DATA DIRECTORY” for InnoDB tables.This is implemented sinceMySQL 5.6.6and can be used only at the creation of the table. It’s not possible to change the DATA DIRECTORY with an ALTER for a normal table(but it’s in some case with partitioned ones as you will see below). If you do so, the option...
InnoDB 是一个非常不错的 MySQL 的存储引擎,目前使用非常广泛基本所有的网站和项目,我想都会优先选择这个,这个也有很好的诊断和微调的工具.我发现其中一个缺点,就是磁盘空间管理时设计非常低效.这个设计成给所有数据都存到 ibdata1 文件,所以这个文件的存储空间会不断的扩展.InnoDB 并不会收缩这些空间,就算你删除表和数据库.所以我们需要注意我们的配置.最好一开始就使用 innodb_file_per_table 的选项.这样可以使得更加灵活性的...
更改数据表中的某一项数据可以使用update命令，具体使用方法如下：如下表，表名为aaa：+----+--------+--------+--------------+| id | nodeId | type | domain |+----+--------+--------+--------------+| 1 | vlsr1 | type1 | blue || 2 | vlsr2 | type2 | red |+----+--------+--------+--------------+想要更改blue为yellow，则使用如下语法：update aaa set domain=yellow where i...
在Mysql中my.ini加上，lower_case_table_names = 0，可以让表的导入导出识别大小写