All kinds of death problems that you encounter when you finish setting up your watch.

Very well do not want to say ...

I've got a new table, and then I've got all kinds of disability.

First of all, the table has completely abandoned the foreign key do not want to build

There was a plan to build a foreign key, but then it was too much trouble and I abandoned it.

Well, let's just make a list of the tangled questions down there.

1. Self-growth

In MySQL, the growth of the keyword is auto_increment, if you need to specify the starting value of the growth, CREATE TABLE XX () after the Auto_increment start value assignment

2, ENGINE = INNODB DEFAULT CHARSET = UTF8

Specifies the engine and character set, the default engine is MyISAM, but MyISAM does not support transactions, it is pass away, and finally a support transaction is selected InnoDB

3. Non-clustered index

It's been a long time for me to tangle PRIMARY key specifies the clustered index directly while setting the primary key, however, I need a unique index in addition to the clustered index, the tragedy, the first search data found in MySQL index is key, it is naïve to write "UserName VARCHAR () KEY ", and then encountered a variety of errors, all kinds of ignore me

Unlike SQL Server, the syntax for MySQL is this--"Unique/index Ix_accountsuser_username (UserName (16))" Index type index name (index field (word length))

This syntax is a bit painful compared to SQL Server syntax ...

4, bit type

bit is either 1 or 0, but the average write value is this. B ' 1 '

5, the date in MySQL (the most painful problem to build the table)
Birthday DATE DEFAULT ' 2012-01-01 '

Birthday DATE DEFAULT ' 2012-01-01 '

Please compare the difference between the above--good a space and then I was in the execution of the cause of my various errors the second I executed the Times are the error and then execute the first one incredibly it's OK. BUT later, it was more painful when I finished the table and now I'm writing this article and I try to use the second one to execute it and times it. It worked

The final conclusion please do not believe SQLyog ... A few more times, there's always food to eat.

5.1, failed to achieve the initialization of

Or is it a date? There are three places in the table structure where a date type is written to the registration date one is the last one is a birthday is a temporary information

First of all, the registration date and birthday were originally designed to use the date type like SQL Server GETDATE () to insert default values directly when inserting records.

--date type does not allow the use of function initialization (date initialization function curdate () or curddent_date ())

In fact, there's a type in MySQL. Timestamp (timestamp) is a date type and can be initialized with a function

But there is a problem with this time stamp in the new version will discard the specified precision is the time stamp accuracy to reach the second level of the temporary information can be used to this date type but the registration date and birthday is completely not accurate to the second level of the need to be abandoned by my ruthless

But found a very interesting thing. TIMESTAMP is a very distinctive type of MySQL that features when you specify this type and add an "on UPDATE current_timestamp" Every time you make a change to a record Fields of type timestamp are synchronized to refresh to the date of the current update

6, add a little nvarchar type

I can't find this type in mysql ...