Tutorial on specifying default values ​​when mysql creates a data table

Specify default value

If no value is given when inserting a row, MySQL allows you to specify the default value to be used at this time. The default value is specified with the DEFAULT keyword in the column definition of the CREATE TABLE statement.

See the following example:

Input:

create table orderitems
(
order_num              int                 NOT NULL,
order_item              int                NOT NULL,
prod_id              char(10)            NOT NULL,
quantity                  int                 NOT NULL   DEFAULT1,
item_price         decimal(8,2)      NOT NULL,
PRIMARY KEY ( order_num,order_item)
)ENGINE = InnoDB;

Output: This statement creates the orderitems table containing the items that make up the order (the order itself is stored in the orders table ). The quantity column contains the quantity of each item in the order. In this example, adding the text DEFAULT 1 to the column's description instructs MySQL to use the quantity 1 if no quantity is given.

Function not allowed Unlike most DBMS, MySQL does not allow the use of functions as default values, it only supports constants.

Use default values ​​instead of NULL values ​​Many database developers use default values ​​instead of NULL columns, especially for columns used in calculations or data grouping.

Extended knowledge:

What is an engine type?

You may have noticed that the CREATE TABLE statements used so far all end with an ENGINE=InnoDB statement.

Like other DBMS, MySQL has an internal engine that specifically manages and processes data. When you use the CREATE TABLE statement, the engine actually creates the table, and when you use the SELECT statement or does other database processing, the engine handles your request internally. Most of the time, this engine is hidden within the DBMS and you don't need to pay too much attention to it.

But MySQL is different from other DBMS in that it has multiple engines. It packages multiple engines, all of which are hidden within the MySQL server, and can all execute commands such as CREATE TABLE and SELECT.

Why release multiple engines? Because they have different functions and features, choosing the right engine for different tasks can provide good functionality and flexibility.

Of course, you can completely ignore these database engines. If the ENGINE= statement is omitted, the default engine is used (most likely MyISAM ), which is used by default for most SQL statements. But not all statements use it by default, which is why the ENGINE= statement is important (and why two engines are used in the sample tables in this book).

The following are a few engines you need to know:

1.InnoDB is a reliable transaction processing engine, it does not support full-text search;

2.MEMORY in function Equivalent to MyISAM, but because the data is stored in memory (not disk), it is very fast (especially suitable for temporary tables);

3.MyISAM is an extremely high-performance engine that supports full-text search. But transaction processing is not supported.

Engine types can be mixed. Except for the productnotes table, which uses MyISAM, the sample tables in this book all use InnoDB. The reason is that the author wants to support transaction processing (hence, use InnoDB ), but also needs to support full text search in productnotes (hence, use MyISAM ).

Foreign keys cannot cross engines. There is a big flaw in mixing engine types. Foreign keys (used to enforce referential integrity) cannot span engines, that is, a table using one engine cannot reference a foreign key that has a table using a different engine.

So, which engine should you use? It depends on what features you need. MyISAM is probably the most popular engine due to its performance and features. But if you don't need reliable transaction processing, you can use other engines.

The above is the detailed content of Tutorial on specifying default values ​​when mysql creates a data table. For more information, please follow other related articles on the PHP Chinese website!