How do I set “MONTHNAME()” function as column default in MySQL?
Actually I need current month name as lowercase string as default value in one of the table column;
DB table may look like Like:
name : john
joined : january
When I try to use mysql function LOWER( MONTHNAME( NOW() ) ) as defailt value this gives me error.
Btw I'm not interested in creating triggers.
any magic queries to do that?
mysql default default-value
asked Nov 21 '18 at 5:41
APuAPu
9214
add a comment |
Actually I need current month name as lowercase string as default value in one of the table column;
DB table may look like Like:
name : john
joined : january
When I try to use mysql function LOWER( MONTHNAME( NOW() ) ) as defailt value this gives me error.
Btw I'm not interested in creating triggers.
any magic queries to do that?
mysql default default-value
asked Nov 21 '18 at 5:41
APuAPu
9214
What is your MySQL version?
– Alexander
Nov 21 '18 at 5:48
Currently I'm using MySQL 5.7.*, by the accepted answer I knew that I can't do it, but be sure to post an answer if you've any trick.
– APu
Nov 21 '18 at 12:38
Btw I can do it exactly stated in the question in MariaDB 10.3 (which still using MySQL 5.7.*), Adding this comment to help someone who's using MariaDB
– APu
Nov 21 '18 at 12:40
add a comment |
Actually I need current month name as lowercase string as default value in one of the table column;
DB table may look like Like:
name : john
joined : january
When I try to use mysql function LOWER( MONTHNAME( NOW() ) ) as defailt value this gives me error.
Btw I'm not interested in creating triggers.
any magic queries to do that?
mysql default default-value
asked Nov 21 '18 at 5:41
APuAPu
9214
Actually I need current month name as lowercase string as default value in one of the table column;
DB table may look like Like:
name : john
joined : january
When I try to use mysql function LOWER( MONTHNAME( NOW() ) ) as defailt value this gives me error.
Btw I'm not interested in creating triggers.
any magic queries to do that?
mysql default default-value
mysql default default-value
asked Nov 21 '18 at 5:41
APuAPu
9214
asked Nov 21 '18 at 5:41
APuAPu
9214
asked Nov 21 '18 at 5:41
APuAPu
9214
asked Nov 21 '18 at 5:41
APuAPu
9214
asked Nov 21 '18 at 5:41
APuAPu
9214
9214
What is your MySQL version?
– Alexander
Nov 21 '18 at 5:48
Currently I'm using MySQL 5.7.*, by the accepted answer I knew that I can't do it, but be sure to post an answer if you've any trick.
– APu
Nov 21 '18 at 12:38
Btw I can do it exactly stated in the question in MariaDB 10.3 (which still using MySQL 5.7.*), Adding this comment to help someone who's using MariaDB
– APu
Nov 21 '18 at 12:40
add a comment |
What is your MySQL version?
– Alexander
Nov 21 '18 at 5:48
Currently I'm using MySQL 5.7.*, by the accepted answer I knew that I can't do it, but be sure to post an answer if you've any trick.
– APu
Nov 21 '18 at 12:38
Btw I can do it exactly stated in the question in MariaDB 10.3 (which still using MySQL 5.7.*), Adding this comment to help someone who's using MariaDB
– APu
Nov 21 '18 at 12:40
What is your MySQL version?
– Alexander
Nov 21 '18 at 5:48
What is your MySQL version?
– Alexander
Nov 21 '18 at 5:48
Currently I'm using MySQL 5.7.*, by the accepted answer I knew that I can't do it, but be sure to post an answer if you've any trick.
– APu
Nov 21 '18 at 12:38
Currently I'm using MySQL 5.7.*, by the accepted answer I knew that I can't do it, but be sure to post an answer if you've any trick.
– APu
Nov 21 '18 at 12:38
Btw I can do it exactly stated in the question in MariaDB 10.3 (which still using MySQL 5.7.*), Adding this comment to help someone who's using MariaDB
– APu
Nov 21 '18 at 12:40
Btw I can do it exactly stated in the question in MariaDB 10.3 (which still using MySQL 5.7.*), Adding this comment to help someone who's using MariaDB
– APu
Nov 21 '18 at 12:40
add a comment |
3 Answers
3
active
oldest
votes
From the MySQL documentation:
With one exception, enclose expression default values within parentheses to distinguish them from literal constant default values.
So, the following should work:
CREATE TABLE yourTable (
month_name VARCHAR(12) DEFAULT (LOWER(MONTHNAME(NOW())))
...
)
As @Madhur has pointed out, using an expression as a default will only work from MySQL 8 onwards.
answered Nov 21 '18 at 5:44
Tim BiegeleisenTim Biegeleisen
219k1388141
1
I think a caveat needs to be added that it will work only from version 8.0.13 and onwards.
– Madhur Bhaiya
Nov 21 '18 at 5:55
Thanks, this is helpful but unfortunately I'm working with 5.7.*, is there any solution for that?
– APu
Nov 21 '18 at 12:33
I think you would need to use a trigger.
– Tim Biegeleisen
Nov 21 '18 at 12:48
add a comment |
If you use MySQL 8.0 or later, then the answers provided by @TimBiegeleisen and @fa06 should solve the problem. In otherwise
... the default value specified in a DEFAULT clause must be a literal constant; it cannot be a function or an expression.
See details in documentation.
So, for previous versions you need to find another way. For example, you could define a trigger that sets default value for the column if the value is not specified:
CREATE TABLE MyTable (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
joined VARCHAR(12)
);
CREATE TRIGGER defaultMonth
BEFORE INSERT ON MyTable
FOR EACH ROW
SET NEW.joined = IFNULL(NEW.joined, LOWER(MONTHNAME(NOW())));
See also live example for the proposed solution.
answered Nov 21 '18 at 16:50
AlexanderAlexander
3,04961329
add a comment |
You can try below
CREATE TABLE orderdata (
order_date DATE
, order_month VARCHAR(50) AS (lower(MONTHNAME(order_date)))
);
answered Nov 21 '18 at 5:45
fa06fa06
11.6k2917
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53405866%2fhow-do-i-set-monthname-function-as-column-default-in-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
From the MySQL documentation:
With one exception, enclose expression default values within parentheses to distinguish them from literal constant default values.
So, the following should work:
CREATE TABLE yourTable (
month_name VARCHAR(12) DEFAULT (LOWER(MONTHNAME(NOW())))
...
)
As @Madhur has pointed out, using an expression as a default will only work from MySQL 8 onwards.
answered Nov 21 '18 at 5:44
Tim BiegeleisenTim Biegeleisen
219k1388141
1
I think a caveat needs to be added that it will work only from version 8.0.13 and onwards.
– Madhur Bhaiya
Nov 21 '18 at 5:55
Thanks, this is helpful but unfortunately I'm working with 5.7.*, is there any solution for that?
– APu
Nov 21 '18 at 12:33
I think you would need to use a trigger.
– Tim Biegeleisen
Nov 21 '18 at 12:48
add a comment |
From the MySQL documentation:
With one exception, enclose expression default values within parentheses to distinguish them from literal constant default values.
So, the following should work:
CREATE TABLE yourTable (
month_name VARCHAR(12) DEFAULT (LOWER(MONTHNAME(NOW())))
...
)
As @Madhur has pointed out, using an expression as a default will only work from MySQL 8 onwards.
answered Nov 21 '18 at 5:44
Tim BiegeleisenTim Biegeleisen
219k1388141
1
I think a caveat needs to be added that it will work only from version 8.0.13 and onwards.
– Madhur Bhaiya
Nov 21 '18 at 5:55
Thanks, this is helpful but unfortunately I'm working with 5.7.*, is there any solution for that?
– APu
Nov 21 '18 at 12:33
I think you would need to use a trigger.
– Tim Biegeleisen
Nov 21 '18 at 12:48
add a comment |
From the MySQL documentation:
With one exception, enclose expression default values within parentheses to distinguish them from literal constant default values.
So, the following should work:
CREATE TABLE yourTable (
month_name VARCHAR(12) DEFAULT (LOWER(MONTHNAME(NOW())))
...
)
As @Madhur has pointed out, using an expression as a default will only work from MySQL 8 onwards.
answered Nov 21 '18 at 5:44
Tim BiegeleisenTim Biegeleisen
219k1388141
From the MySQL documentation:
With one exception, enclose expression default values within parentheses to distinguish them from literal constant default values.
So, the following should work:
CREATE TABLE yourTable (
month_name VARCHAR(12) DEFAULT (LOWER(MONTHNAME(NOW())))
...
)
As @Madhur has pointed out, using an expression as a default will only work from MySQL 8 onwards.
answered Nov 21 '18 at 5:44
Tim BiegeleisenTim Biegeleisen
219k1388141
edited Nov 21 '18 at 5:56
answered Nov 21 '18 at 5:44
Tim BiegeleisenTim Biegeleisen
219k1388141
answered Nov 21 '18 at 5:44
Tim BiegeleisenTim Biegeleisen
219k1388141
answered Nov 21 '18 at 5:44
Tim BiegeleisenTim Biegeleisen
219k1388141
219k1388141
1
I think a caveat needs to be added that it will work only from version 8.0.13 and onwards.
– Madhur Bhaiya
Nov 21 '18 at 5:55
Thanks, this is helpful but unfortunately I'm working with 5.7.*, is there any solution for that?
– APu
Nov 21 '18 at 12:33
I think you would need to use a trigger.
– Tim Biegeleisen
Nov 21 '18 at 12:48
add a comment |
1
I think a caveat needs to be added that it will work only from version 8.0.13 and onwards.
– Madhur Bhaiya
Nov 21 '18 at 5:55
Thanks, this is helpful but unfortunately I'm working with 5.7.*, is there any solution for that?
– APu
Nov 21 '18 at 12:33
I think you would need to use a trigger.
– Tim Biegeleisen
Nov 21 '18 at 12:48
1
1
I think a caveat needs to be added that it will work only from version 8.0.13 and onwards.
– Madhur Bhaiya
Nov 21 '18 at 5:55
I think a caveat needs to be added that it will work only from version 8.0.13 and onwards.
– Madhur Bhaiya
Nov 21 '18 at 5:55
Thanks, this is helpful but unfortunately I'm working with 5.7.*, is there any solution for that?
– APu
Nov 21 '18 at 12:33
Thanks, this is helpful but unfortunately I'm working with 5.7.*, is there any solution for that?
– APu
Nov 21 '18 at 12:33
I think you would need to use a trigger.
– Tim Biegeleisen
Nov 21 '18 at 12:48
I think you would need to use a trigger.
– Tim Biegeleisen
Nov 21 '18 at 12:48
add a comment |
If you use MySQL 8.0 or later, then the answers provided by @TimBiegeleisen and @fa06 should solve the problem. In otherwise
... the default value specified in a DEFAULT clause must be a literal constant; it cannot be a function or an expression.
See details in documentation.
So, for previous versions you need to find another way. For example, you could define a trigger that sets default value for the column if the value is not specified:
CREATE TABLE MyTable (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
joined VARCHAR(12)
);
CREATE TRIGGER defaultMonth
BEFORE INSERT ON MyTable
FOR EACH ROW
SET NEW.joined = IFNULL(NEW.joined, LOWER(MONTHNAME(NOW())));
See also live example for the proposed solution.
answered Nov 21 '18 at 16:50
AlexanderAlexander
3,04961329
add a comment |
If you use MySQL 8.0 or later, then the answers provided by @TimBiegeleisen and @fa06 should solve the problem. In otherwise
... the default value specified in a DEFAULT clause must be a literal constant; it cannot be a function or an expression.
See details in documentation.
So, for previous versions you need to find another way. For example, you could define a trigger that sets default value for the column if the value is not specified:
CREATE TABLE MyTable (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
joined VARCHAR(12)
);
CREATE TRIGGER defaultMonth
BEFORE INSERT ON MyTable
FOR EACH ROW
SET NEW.joined = IFNULL(NEW.joined, LOWER(MONTHNAME(NOW())));
See also live example for the proposed solution.
answered Nov 21 '18 at 16:50
AlexanderAlexander
3,04961329
add a comment |
If you use MySQL 8.0 or later, then the answers provided by @TimBiegeleisen and @fa06 should solve the problem. In otherwise
... the default value specified in a DEFAULT clause must be a literal constant; it cannot be a function or an expression.
See details in documentation.
So, for previous versions you need to find another way. For example, you could define a trigger that sets default value for the column if the value is not specified:
CREATE TABLE MyTable (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
joined VARCHAR(12)
);
CREATE TRIGGER defaultMonth
BEFORE INSERT ON MyTable
FOR EACH ROW
SET NEW.joined = IFNULL(NEW.joined, LOWER(MONTHNAME(NOW())));
See also live example for the proposed solution.
answered Nov 21 '18 at 16:50
AlexanderAlexander
3,04961329
If you use MySQL 8.0 or later, then the answers provided by @TimBiegeleisen and @fa06 should solve the problem. In otherwise
... the default value specified in a DEFAULT clause must be a literal constant; it cannot be a function or an expression.
See details in documentation.
So, for previous versions you need to find another way. For example, you could define a trigger that sets default value for the column if the value is not specified:
CREATE TABLE MyTable (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
joined VARCHAR(12)
);
CREATE TRIGGER defaultMonth
BEFORE INSERT ON MyTable
FOR EACH ROW
SET NEW.joined = IFNULL(NEW.joined, LOWER(MONTHNAME(NOW())));
See also live example for the proposed solution.
answered Nov 21 '18 at 16:50
AlexanderAlexander
3,04961329
edited Nov 22 '18 at 1:47
answered Nov 21 '18 at 16:50
AlexanderAlexander
3,04961329
answered Nov 21 '18 at 16:50
AlexanderAlexander
3,04961329
answered Nov 21 '18 at 16:50
AlexanderAlexander
3,04961329
3,04961329
add a comment |
add a comment |
You can try below
CREATE TABLE orderdata (
order_date DATE
, order_month VARCHAR(50) AS (lower(MONTHNAME(order_date)))
);
answered Nov 21 '18 at 5:45
fa06fa06
11.6k2917
add a comment |
You can try below
CREATE TABLE orderdata (
order_date DATE
, order_month VARCHAR(50) AS (lower(MONTHNAME(order_date)))
);
answered Nov 21 '18 at 5:45
fa06fa06
11.6k2917
add a comment |
You can try below
CREATE TABLE orderdata (
order_date DATE
, order_month VARCHAR(50) AS (lower(MONTHNAME(order_date)))
);
answered Nov 21 '18 at 5:45
fa06fa06
11.6k2917
You can try below
CREATE TABLE orderdata (
order_date DATE
, order_month VARCHAR(50) AS (lower(MONTHNAME(order_date)))
);
answered Nov 21 '18 at 5:45
fa06fa06
11.6k2917
answered Nov 21 '18 at 5:45
fa06fa06
11.6k2917
answered Nov 21 '18 at 5:45
fa06fa06
11.6k2917
answered Nov 21 '18 at 5:45
fa06fa06
11.6k2917
11.6k2917
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53405866%2fhow-do-i-set-monthname-function-as-column-default-in-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
What is your MySQL version?
– Alexander
Nov 21 '18 at 5:48
Currently I'm using MySQL 5.7.*, by the accepted answer I knew that I can't do it, but be sure to post an answer if you've any trick.
– APu
Nov 21 '18 at 12:38
Btw I can do it exactly stated in the question in MariaDB 10.3 (which still using MySQL 5.7.*), Adding this comment to help someone who's using MariaDB
– APu
Nov 21 '18 at 12:40