how to calculate diff year with non-standard datetime format in mysql

Question

My database(MySQL 5.7) store the user birthday format like this 1990年1月1日, now I want to calculate the user age from this format datetime. My sql look like this but return null for all data:

select period_diff(date_format(now(), '%Y'), date_format(birthday, '%Y年')) as months 
from spark_user;

the table DDL look like this:

CREATE TABLE `spark_user` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `birthday` varchar(64) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`),
 KEY `index_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8mb4;

I also tried like this:

select period_diff(date_format(now(), '%Y'), date_format(STR_TO_DATE(birthday, '%Y'), '%Y')) as months 
from spark_user;

this works:

 select period_diff( date_format(STR_TO_DATE(birthday, '%Y年%m月%d日'), '%Y'),date_format(now(), '%Y')) as months,
birthday as birthday 
from spark_user;

but the result is strange:

57 1990年1月1日
57 1990年1月1日
57 1990年1月1日
57 1990年10月10日
47 1980年1月1日
-20 2001年1月1日
63 1996年10月10日

• Akina: Convert to standard DATE then calculate.

Answer 1

YEAR(CURDATE()) - LEFT(birthday, 4)

Note: It will be off by up to 1 year, depending on the current date and when in the year they were born.

Answer 2

store the user birthday format like this ...

One of the many unwritten Rules about Relational databases -

Dates have no format ...

... or, at least, none that you or I need worry about.

If you think that your Dates have a "format", then you're looking almost certainly at Character data, not proper Dates and, as you've discovered, trying to work with character data that just happens to contain Date values is difficult.

Always use the Right Data Type for the Right Job.

Sure, STR_TO_DATE converts a character value into a Date value, but does so according to the database's own rules.
When, exactly, is 01/04/07? January? April? 2001 or 2007?
(You'll find the one that works for you and all will be well - until you have to port your database to some other DBMS, with different rules and all Hell breaks loose).
Also, using a function into this way prevents the database from using any index you have on that field - Table Scanning, here we come.

Answer 3

I use this sql command, works:

select YEAR(now()) - YEAR(STR_TO_DATE(birthday, '%Y年%m月%d日'))