Mysql年龄更新.... [英] Mysql Age Update....
本文介绍了Mysql年龄更新....的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
mysql> use sginfo;
Database changed
mysql> describe personinfo;
+------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+--------------+------+-----+---------+-------+
| surname | varchar(255) | YES | | NULL | |
| firstname | varchar(255) | YES | | NULL | |
| middlename | varchar(255) | YES | | NULL | |
| present_currentaddress | varchar(255) | YES | | NULL | |
| provincialaddress | varchar(255) | YES | | NULL | |
| noofchildren | varchar(255) | YES | | NULL | |
| height | varchar(255) | YES | | NULL | |
| weight | varchar(255) | YES | | NULL | |
| birthdate | varchar(255) | YES | | NULL | |
| age | varchar(255) | YES | | NULL | |
| birthplace | varchar(255) | YES | | NULL | |
| gender | varchar(255) | YES | | NULL | |
| civilstatus | varchar(255) | YES | | NULL | |
| educationalattain | varchar(255) | YES | | NULL | |
| schoolname | varchar(255) | YES | | NULL | |
| course | varchar(255) | YES | | NULL | |
| yearsofstudy | varchar(255) | YES | | NULL | |
| collegestatus | varchar(255) | YES | | NULL | |
| trainingschoolname | varchar(255) | YES | | NULL | |
| positiontrained | varchar(255) | YES | | NULL | |
| yearstrained | varchar(255) | YES | | NULL | |
| coursetrained | varchar(255) | YES | | NULL | |
| detachment | varchar(255) | YES | | NULL | |
| region | varchar(255) | YES | | NULL | |
| effdate | varchar(255) | YES | | NULL | |
| workstatus | varchar(255) | YES | | NULL | |
| empnumber | varchar(255) | YES | | NULL | |
| positionemployed | varchar(255) | YES | | NULL | |
| yearsofemployed | varchar(255) | YES | | NULL | |
| sssnumber | varchar(255) | YES | | NULL | |
| tinnumber | varchar(255) | YES | | NULL | |
| nbi | varchar(255) | YES | | NULL | |
| nbidate | varchar(255) | YES | | NULL | |
| pnp | varchar(255) | YES | | NULL | |
| pnpdate | varchar(255) | YES | | NULL | |
| priorexp | varchar(255) | YES | | NULL | |
| gdexp | varchar(255) | YES | | NULL | |
| guardposition | varchar(255) | YES | | NULL | |
| uniformdate | varchar(255) | YES | | NULL | |
| millitary | varchar(255) | YES | | NULL | |
| los | varchar(255) | YES | | NULL | |
| previousemp | varchar(255) | YES | | NULL | |
| licno | varchar(255) | YES | | NULL | |
| licdate | varchar(1) | YES | | NULL | |
| licexp | varchar(255) | YES | | NULL | |
| dateofemp | varchar(255) | YES | | NULL | |
| badgeno | varchar(255) | YES | | NULL | |
| neurocenter | varchar(255) | YES | | NULL | |
| neuroresult | varchar(255) | YES | | NULL | |
| neurodate | varchar(255) | YES | | NULL | |
| drugcenter | varchar(255) | YES | | NULL | |
| marijuana | varchar(255) | YES | | NULL | |
| shabu | varchar(255) | YES | | NULL | |
| drugresult | varchar(255) | YES | | NULL | |
| agency | varchar(255) | YES | | NULL | |
| histories | text | YES | | NULL | |
| agepattern | varchar(255) | YES | | NULL | |
+------------------------+--------------+------+-----+---------+-------+
57 rows in set (0.01 sec)
mysql> select birthdate from personinfo limit 1;
+-----------+
| birthdate |
+-----------+
| 28-Apr-77 |
+-----------+
1 row in set (0.00 sec)
mysql> UPDATE personinfo SET age = DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(
birthdate)), '%Y')+0;
ERROR 1292 (22007): Truncated incorrect datetime value: '28-Apr-77'
mysql>
正如你在上面我的例子中看到的，我有一个带有生日出生日期的表格，我现在向您展示一个示例数据，我的问题是我的代码在它遇到生日时更新所有年龄似乎是错误的。有人可以帮我弄这个吗？ TY：D
As you can see on my example above i have a table with a field birthdate and i show you an example data of it now my question is that my code for updating all the age when it meets its birthday seems wrong. can someone help me with this? TY :D
推荐答案
首先，必须使用mysql中的str_to_date函数将birthdate转换为datetime类型，以便将varchar转换为datetime，参见document 在哪里。
其次，实际上没有必要在数据库中存储年龄，因为它是一个可导出的移动数字，最好从代码中的birthdate派生。
第三，更好地更改所有日期字段，例如。 birthdate，dateofemp等，到datetime数据类型。您应该能够在网上找到示例。
First, the birthdate have to be converted to datetime type using str_to_date function in mysql to convert from varchar to datetime, see document here.
Secondly, there is really no need to store age in database as it is a moving number that is derivable and is best derived from birthdate in code.
Thirdly, better to change all the date fields, eg. birthdate, dateofemp etc, to datetime data type. You should be able to find examples on the web.
这篇关于Mysql年龄更新....的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
