MySQL - Selecting only the default value from column

问题: I am wondering is it possible to select only the default value of empty column? I have absolutely empty table and I want just to select one of the columns default value - i...

问题:

I am wondering is it possible to select only the default value of empty column? I have absolutely empty table and I want just to select one of the columns default value - it is important for my JAVA app which is filling the table.

Thanks.


回答1:

You can get the default from the INFORMATION_SCHEMA.COLUMNS

select COLUMN_DEFAULT
from INFORMATION_SCHEMA.COLUMNS 
where TABLE_SCHEMA='your_db' and TABLE_NAME='your_table' and COLUMN_NAME='your_column'

回答2:

You can define a default value for a column when you create a table, if you just want MySQL to insert it automatically:

create table my_table (i INT DEFAULT 1);

But if you mean you want the default value which is stored in the DB dictionary, you can get it by this query:

SELECT Column_Default
  FROM Information_Schema.Columns
WHERE Table_Schema = 'yourSchema'
  AND Table_Name = 'yourTableName'
  AND Column_Name = 'yourColumnName'

回答3:

I can only think of two ways:

Inserting a row

  1. Insert a row without specifying a value for that column
  2. Select the column from that row; it will have the default value of the column
  3. Delete the row

...probably all in a transaction so nothing else sees it.

Using describe (explain)

The describe command (aka explain) describes objects in the system, including tables. So if you do explain YourTable, you'll get back information about the table, including its default values.

Here's an example from that linked documentation:

mysql> DESCRIBE City;
+−−−−−−−−−−−−+−−−−−−−−−−+−−−−−−+−−−−−+−−−−−−−−−+−−−−−−−−−−−−−−−−+
| Field      | Type     | Null | Key | Default | Extra          |
+−−−−−−−−−−−−+−−−−−−−−−−+−−−−−−+−−−−−+−−−−−−−−−+−−−−−−−−−−−−−−−−+
| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name       | char(35) | NO   |     |         |                |
| Country    | char(3)  | NO   | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  | NO   |     | 0       |                |
+−−−−−−−−−−−−+−−−−−−−−−−+−−−−−−+−−−−−+−−−−−−−−−+−−−−−−−−−−−−−−−−+

So you can extract the default from the Default column in the returned rows.


Ah, of course, there's a third way, see slaakso's answer for it.

  • 发表于 2019-03-22 16:58
  • 阅读 ( 183 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除