mysql中包含日期 - 年份的日期所需的数据类型 [英] Data type required in a mysql for a date containing day-month-year
本文介绍了mysql中包含日期 - 年份的日期所需的数据类型的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想使用DATE_FORMAT（）函数获取一个示例mysql查询。我看过的大部分帖子都使用SELECT和SELECT *。但我相信这应该用UPDATE替代。如UPDATE用户SET dob = DATE_FORMAT（dob，'％m /％d /％Y'））; 。
请找到我的代码，了解如何在出生日期之前指定：
< label id =dob for =dob>出生日期：< / label>
<！ - 月份下拉列表 - >
< select name =dobid =monthonchange =size =1>
< option value =01> January< / option>
< option value =02> February< / option>
< option value =03> March< / option>
< option value =04> April< / option>
< option value =05> May< / option>
< option value =06> June< / option>
< option value =07> July< / option>
< option value =08> August< / option>
< option value =09> September< / option>
< option value =10> October< / option>
< option value =11> November< / option>
< option value =12> December< / option>
< / select>
<！ - 日下拉菜单 - >
< select name =dobid =dayonchange =size =1>
< option value =01> 01< / option>
< option value =02> 02< / option>
< option value =03> 03< / option>
< option value =04> 04< / option>
< option value =05> 05< / option>
< option value =06> 06< / option>
< option value =07> 07< / option>
< option value =08> 08< / option>
< option value =09> 09< / option>
< option value =10> 10< / option>
< option value =11> 11< / option>
< option value =12> 12< / option>
< option value =13> 13< / option>
< option value =14> 14< / option>
< option value =15> 15< / option>
< option value =16> 16< / option>
< option value =17> 17< / option>
< option value =18> 18< / option>
< option value =19> 19< / option>
< option value =20> 20< / option>
< option value =21> 21< / option>
< option value =22> 22< / option>
< option value =23> 23< / option>
< option value =24> 24< / option>
< option value =25> 25< / option>
< option value =26> 26< / option>
< option value =27> 27< / option>
< option value =28> 28< / option>
< option value =29> 29< / option>
< option value =30> 30< / option>
< option value =31> 31< / option>
< / select>
< select id =birthyearname =dob>
< option value =1996> 1996< / option>
< option value =1995> 1995< / option>
< option value =1994> 1994< / option>
< option value =1993> 1993< / option>
< option value =1992> 1992< / option>
< option value =1991> 1991< / option>
< option value =1990> 1990< / option>
< option value =1989> 1989< / option>
< option value =1988> 1988< / option>
< option value =1987> 1987< / option>
< option value =1986> 1986< / option>
< option value =1985> 1985< / option>
< option value =1984> 1984< / option>
< option value =1983> 1983< / option>
< option value =1982> 1982< / option>
< option value =1981> 1981< / option>
< option value =1980> 1980< / option>
< option value =1979> 1979< / option>
< option value =1978> 1978< / option>
< option value =1977> 1977< / option>
< option value =1976> 1976< / option>
< option value =1975> 1975< / option>
< option value =1974> 1974< / option>
< option value =1973> 1973< / option>
< option value =1972> 1972< / option>
< option value =1971> 1971< / option>
< option value =1970> 1970< / option>
< option value =1969> 1969< / option>
< option value =1968> 1968< / option>
< option value =1967> 1967< / option>
< option value =1966> 1966< / option>
< option value =1965> 1965< / option>
< option value =1964> 1964< / option>
< option value =1963> 1963< / option>
< option value =1962> 1962< / option>
< option value =1961> 1961< / option>
< option value =1960> 1960< / option>
< option value =1959> 1959< / option>
< option value =1958> 1958< / option>
< option value =1957> 1957< / option>
< option value =1956> 1956< / option>
< option value =1955> 1955< / option>
< option value =1954> 1954< / option>
< option value =1953> 1953< / option>
< option value =1952> 1952< / option>
< option value =1951> 1951< / option>
< option value =1950> 1950< / option>
< option value =1949> 1949< / option>
< option value =1948> 1948< / option>
< option value =1947> 1947< / option>
< option value =1946> 1946< / option>
< option value =1945> 1945< / option>
< option value =1944> 1944< / option>
< option value =1943> 1943< / option>
< option value =1942> 1942< / option>
< option value =1941> 1941< / option>
< option value =1940> 1940< / option>
< option value =1939> 1939< / option>
< option value =1938> 1938< / option>
< option value =1937> 1937< / option>
< option value =1936> 1936< / option>
< option value =1935> 1935< / option>
< option value =1934> 1934< / option>
< option value =1933> 1933< / option>
< option value =1932> 1932< / option>
< option value =1931> 1931< / option>
< option value =1930> 1930< / option>
< option value =1929> 1929< / option>
< option value =1928> 1928< / option>
< option value =1927> 1927< / option>
< option value =1926> 1926< / option>
< option value =1925> 1925< / option>
< option value =1924> 1924< / option>
< option value =1923> 1923< / option>
< option value =1922> 1922< / option>
< option value =1921> 1921< / option>
< option value =1920> 1920< / option>
< / select>
< / p>
解决方案
在数据库中保存日期时，请使用正确的数据类型 DATE 或 DATETIME 。不要使用 VARCHAR
优点：
- 易于操作
- 在操作数据时不需要投射 将使用
- 索引（如果键为定义）
缺点
- 不能想到的东西..
如果你想以这种格式保存日期的原因 1996年1月1日是因为它比 1996-01-01 更可读，那么不要。这可以在数据投影期间使用 DATE_FORMAT（）实现。
Hi guys I'm using wamp server and I have a database with a date of birth field. I would simply like to know what data type would be suitable for the way date of birth is specified on my website (January 01 1996). I would like to get a sample mysql query using the DATE_FORMAT() function. Most of the posts I've seen use SELECT and SELECT*. But I believe this should be replaced with UPDATE. As in UPDATE users SET dob = DATE_FORMAT(dob, '%m/%d/%Y')); . Please find my code for how the date of birth is specified below:
<label id= "dob" for="dob">Date of birth:</label>
<!-- Month dropdown -->
<select name="dob" id="month" onchange="" size="1">
<option value="01">January</option>
<option value="02">February</option>
<option value="03">March</option>
<option value="04">April</option>
<option value="05">May</option>
<option value="06">June</option>
<option value="07">July</option>
<option value="08">August</option>
<option value="09">September</option>
<option value="10">October</option>
<option value="11">November</option>
<option value="12">December</option>
</select>
<!-- Day dropdown -->
<select name="dob" id="day" onchange="" size="1">
<option value="01">01</option>
<option value="02">02</option>
<option value="03">03</option>
<option value="04">04</option>
<option value="05">05</option>
<option value="06">06</option>
<option value="07">07</option>
<option value="08">08</option>
<option value="09">09</option>
<option value="10">10</option>
<option value="11">11</option>
<option value="12">12</option>
<option value="13">13</option>
<option value="14">14</option>
<option value="15">15</option>
<option value="16">16</option>
<option value="17">17</option>
<option value="18">18</option>
<option value="19">19</option>
<option value="20">20</option>
<option value="21">21</option>
<option value="22">22</option>
<option value="23">23</option>
<option value="24">24</option>
<option value="25">25</option>
<option value="26">26</option>
<option value="27">27</option>
<option value="28">28</option>
<option value="29">29</option>
<option value="30">30</option>
<option value="31">31</option>
</select>
<select id="birthyear" name="dob">
<option value="1996">1996</option>
<option value="1995">1995</option>
<option value="1994">1994</option>
<option value="1993">1993</option>
<option value="1992">1992</option>
<option value="1991">1991</option>
<option value="1990">1990</option>
<option value="1989">1989</option>
<option value="1988">1988</option>
<option value="1987">1987</option>
<option value="1986">1986</option>
<option value="1985">1985</option>
<option value="1984">1984</option>
<option value="1983">1983</option>
<option value="1982">1982</option>
<option value="1981">1981</option>
<option value="1980">1980</option>
<option value="1979">1979</option>
<option value="1978">1978</option>
<option value="1977">1977</option>
<option value="1976">1976</option>
<option value="1975">1975</option>
<option value="1974">1974</option>
<option value="1973">1973</option>
<option value="1972">1972</option>
<option value="1971">1971</option>
<option value="1970">1970</option>
<option value="1969">1969</option>
<option value="1968">1968</option>
<option value="1967">1967</option>
<option value="1966">1966</option>
<option value="1965">1965</option>
<option value="1964">1964</option>
<option value="1963">1963</option>
<option value="1962">1962</option>
<option value="1961">1961</option>
<option value="1960">1960</option>
<option value="1959">1959</option>
<option value="1958">1958</option>
<option value="1957">1957</option>
<option value="1956">1956</option>
<option value="1955">1955</option>
<option value="1954">1954</option>
<option value="1953">1953</option>
<option value="1952">1952</option>
<option value="1951">1951</option>
<option value="1950">1950</option>
<option value="1949">1949</option>
<option value="1948">1948</option>
<option value="1947">1947</option>
<option value="1946">1946</option>
<option value="1945">1945</option>
<option value="1944">1944</option>
<option value="1943">1943</option>
<option value="1942">1942</option>
<option value="1941">1941</option>
<option value="1940">1940</option>
<option value="1939">1939</option>
<option value="1938">1938</option>
<option value="1937">1937</option>
<option value="1936">1936</option>
<option value="1935">1935</option>
<option value="1934">1934</option>
<option value="1933">1933</option>
<option value="1932">1932</option>
<option value="1931">1931</option>
<option value="1930">1930</option>
<option value="1929">1929</option>
<option value="1928">1928</option>
<option value="1927">1927</option>
<option value="1926">1926</option>
<option value="1925">1925</option>
<option value="1924">1924</option>
<option value="1923">1923</option>
<option value="1922">1922</option>
<option value="1921">1921</option>
<option value="1920">1920</option>
</select>
</p>
解决方案
When saving date in the database, use the correct datatype DATE or DATETIME. Don't use VARCHAR
Pros:
- easy to manipulate
- requires no casting when manipulating data
- index will be used (if a key was defined)
Cons
- can't think of something..
If the reason why you want to save date in this format January 01 1996 is because it is more readable than 1996-01-01, then don't. This can be achieve using DATE_FORMAT() during the projection of the data.
这篇关于mysql中包含日期 - 年份的日期所需的数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
