©SQL :: Manager
HomeProgram InformationTechnical detailsFree downloadDonate for a cup of coffee
myway SQL Manager for MySQL and MariaDB, Download on Uptodown
SQL functionMySQLMariaDB

The DEFAULT SQL Function in MySQL and MariaDB - Get default value of table column

DEFAULTSyntax:DEFAULT(Column)
Return value:Depending on the data types used
Function type:Information function
Function Description

The DEFAULT() function returns the default value for a table column.

If the column has no default value and cannot be NULL, an error is returned.

If the column has no default value and can be NULL, NULL is returned.

For integer columns using AUTO_INCREMENT, 0 is returned.

SQL Examples for the DEFAULT function

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30


DROP TABLE IF EXISTS `test_table`;
    
CREATE TABLE `test_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NULL DEFAULT 'no name',
  `last` varchar(20) default null,
  `type` enum('First','Second','Third','Last'DEFAULT NULL,
  `set` set('First','Second','Third','Last'DEFAULT NULL,
  `num1` int(11) NOT NULL DEFAULT 0,
  `num2` int(11) NOT NULL DEFAULT 2,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

INSERT INTO `test_table` (`id`,`name`,`last`,`type`,`set`,`num1`,`num2`VALUES 
(1,'Art 1','a','First','Last',1,7),
(2,'Art 2','b','Second','First,Last',3,7),
(3,'Art 3','c','Second','Second,Last',5,7),
(4,'Art 4','d','Third','First,Second,Last',5,7),
(5,'Art 5','e','Last','First,Last',7,15),
(6,'Art 6','f','First','First,Last',7,15),
(7,'Art 7','g','Third','First,Third,Last',9,15),
(8,'Art 8','','Last','Second,Third,Last',9,15);

select * from `test_table`;

select id, default(id), name, default(name), `last`default(`last`), num1, default(num1), num2, default(num2) from test_table;

id
name
last
type
set
num1
num2
int(11)varchar(20) LATIN1varchar(20) LATIN1enum LATIN1set LATIN1int(11)int(11)
1Art 1aFirstLast17
2Art 2bSecondFirst,Last37
3Art 3cSecondSecond,Last57
4Art 4dThirdFirst,Second,Last57
5Art 5eLastFirst,Last715
6Art 6fFirstFirst,Last715
7Art 7gThirdFirst,Third,Last915
8Art 8 LastSecond,Third,Last915
id
default(id)
name
default(name)
last
default(`last`)
num1
default(num1)
num2
default(num2)
id
int(11)
id
int(11)
name
varchar(20) LATIN1
name
varchar(20) LATIN1
last
varchar(20) LATIN1
last
varchar(20) LATIN1
num1
int(11)
num1
int(11)
num2
int(11)
num2
int(11)
10Art 1no nameaNULL1072
20Art 2no namebNULL3072
30Art 3no namecNULL5072
40Art 4no namedNULL5072
50Art 5no nameeNULL70152
60Art 6no namefNULL70152
70Art 7no namegNULL90152
80Art 8no name NULL90152
The examples were created with the MyWAY SQL manager: Download

How to use the DEFAULT() function in MySQL and MariaDB databases

In MySQL and MariaDB the DEFAULT() function is used to get the default value of a in a table structure defined column and returns the value that is inserted into a table column if no explicit value is supplied during an INSERT operation. The function takes the name of a column as an argument and can be used within INSERT statements or SELECT statements to get the default value of a column .

When inserting data into a table, the DEFAULT() function can be used to specify that the default value defined for a column should be used. Furthermore, the function can be used in a SELECT statement to retrieve the default value of a column, for example to display or use it in query results. The function can also be used in conditional statements or expressions to handle cases where the default value should be used based on certain conditions.
DAYOFYEARDECODEDECODE_ORACLEDEFAULTDEGREESDES_DECRYPTDES_ENCRYPT

Further MySQL und MariaDB SQL Information functions

BENCHMARKBENCHMARK(Number, Expression)
More about BENCHMARK Function

BINLOG_GTID_POSBINLOG_GTID_POS()

CHARSETCHARSET(String)
More about CHARSET Function

COERCIBILITYCOERCIBILITY(String)
More about COERCIBILITY Function

COLLATIONCOLLATION(String)
More about COLLATION Function

CONNECTION_IDCONNECTION_ID()
More about CONNECTION_ID Function

CURRENT_USERCURRENT_USER()
More about CURRENT_USER Function

CURRENT_ROLECURRENT_ROLE()
More about CURRENT_ROLE Function

DATABASEDATABASE()
More about DATABASE Function

DECODE_HISTOGRAMDECODE_HISTOGRAM()

FOUND_ROWSFOUND_ROWS()
More about FOUND_ROWS Function

LAST_INSERT_IDLAST_INSERT_ID([Expression])
More about LAST_INSERT_ID Function

LAST_VALUELAST_VALUE(Expression [, Expression ...])
More about LAST_VALUE Function

ROWNUMROWNUM()

ROW_COUNTROW_COUNT()
More about ROW_COUNT Function

SCHEMASCHEMA()
More about SCHEMA Function

SESSION_USERSESSION_USER()
More about SESSION_USER Function

SYSTEM_USERSYSTEM_USER()
More about SYSTEM_USER Function

USERUSER()
More about USER Function

VERSIONVERSION()
More about VERSION Function

Numeric functionsString functionsRegular ExpressionsDate and Time functions
Comparison functionsEncryption & CompressionConversion functionsNULL functions
Aggregate functionsWindow functionsJSON functionsGeometric functions
Sequence functionsInformation functionsDynamic ColumnsMiscellaneous functions
More information about the DEFAULT SQL function: mysql.com and mariadb.com
Updated: 2023-09-17ImprintContactTerms & conditionsPrivacy & CookiesUpdatesSitemapFacebookLinkedinTwitterStatistics©2020-2024 Heino Cunze-Fischer