… Schema CREATE TABLE `parent` ( `parent_id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`parent_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `child_A` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `parent…
Post: The small improvements of MySQL 5.6: Duplicate Index Detection
May 31, 2013 By Leave a Comment
…: 0 mysql> SHOW CREATE TABLE test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `col2` int(11…: # `col2` int(11) default null # `id` int(11) not null auto_increment # To shorten this duplicate clustered index, execute: ALTER TABLE `test…
Post: Replication in MySQL 5.6: GTIDs benefits and limitations - Part 1
May 21, 2013 By 8 Comments
… master to master_auto_position = 1; mysql> start slave; and let’s create a new table on the master: mysql> create table test.t (id int not null auto_increment primary key); Executing SHOW TABLES FROM test on both slaves shows that the table has been created everywhere. So…
Comment: Derived Tables and Views Performance
May 21, 2013 By 16 Comments
….html create dynamic main menu and sub menu using php and mysql CREATE TABLE `menu` ( `id` int(11) NOT NULL auto_increment, `label…
Post: Follow these basics when migrating to Percona XtraDB Cluster for MySQL
May 1, 2013 By 10 Comments
… than one PXC node. This is because before Galera can create writesets for the replicated events, binlog events must be generated… simply cannot replace in short term. Control Your Auto-Incrementing Columns PXC/Galera controls auto-incrementing values internally within the cluster, this is to…
Post: Understanding the maximum number of columns in a MySQL table
April 8, 2013 By 5 Comments
… things like NEXT_NUMBER fields (what we know today as auto_increment), CASEUP and CASEDN fields which although not implemented in MySQL… (accepts command line parameter of number of columns to create) to produce the CREATE TABLE sql statement: sub cname ($) { my $c=shift…; } $name.=’0′ foreach(length $name .. shift); return $name } my $sql= “CREATE TABLE `”.cname(16,63).”` (“; foreach(1..shift @ARGV) { my $n…
Post: AUTO_INCREMENT and MERGE TABLES
September 11, 2008 By Leave a Comment
… row will cause auto_increment value to be reused too. Can you use AUTO_INCREMENT clause in CREATE TABLE to get different auto_increment values ? I guess not: mysql> alter table am auto_increment=1000; Query OK…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 8 Comments
…); Our InnoDB table with auto increment column will be like this: CREATE TABLE `foo` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` int(11) DEFAULT… check the auto_increment counter: show create table foo\G *************************** 1. row *************************** Table: foo Create Table: CREATE TABLE `foo` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` int…
Post: Sharing an auto_increment value across multiple MySQL tables
October 4, 2010 By 9 Comments
… into, and grab the insert_id: CREATE TABLE option1 (id int not null primary key auto_increment) engine=innodb; # each insert does one…: Create a new sequence value insert into another table The testing options are: Option 1 – auto_commit each statement Option 2 – auto_commit… you have already started modifying data before you need an auto_increment number but you do not want to commit yet. Full…
Post: Recovery after DROP & CREATE
August 22, 2012 By 1 Comment
… SET character_set_client = utf8 */; CREATE TABLE `actor` ( `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(45) NOT NULL… the dropped table gets overwritten. mysql> CREATE TABLE actor ( -> actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, -> first_name VARCHAR(45) NOT NULL…
