What should be the type of row in the database for a phone number consisting of only digits?

Question

MySQL

There is a row in the phone table, numbers in the form (000)-00-00-000 get there. Is there a way to have the DB clean up unwanted characters when they hit? It is necessary that the number gets there in the format 0000000000. The
mask for entering the number on the site cannot be disabled or changed.

Answer 1

As far as I understand, it is necessary not only to clean them for saving and subsequent convenient use, but also to return them to their place when transferring data back to the site for display or correction, right?
If so, then I suggest not to do anything with the field itself, let it be textual and with all sorts of garbage. And for use, create another, calculated, field in which to put the cleared and reduced to a numeric value.
Those. if currently available

CREATE TABLE users (
    ...
    phone VARCHAR(255),
    ....
);

then we execute

ALTER TABLE users ALGORITHM = INPLACE
    ADD COLUMN phone_num BIGINT UNSIGNED AS (REGEXP_REPLACE(phone, '[^0-9], ''')) VIRTUAL,
    INDEX idx_phone_num (phone_num);

What does it give?
A calculated field expression strips the string value of anything that is not a digit, after which the value is converted to the field's data type, i.e. BIGINT UNSIGNED. Okay, got the number.
The field is declared as VIRTUAL. This means that its value is not stored in a table, but is calculated each time it is needed. But we create an index on this field. Accordingly, the server may not calculate the value, but extract it from the index. Even better, if this field is included in composite indexes instead of the phone field, then the likelihood that the server will retrieve the value from the index will increase.
Of course, it would be possible to make the STORED field and not fool around with the index. But then the process of changing the table structure will be quite lengthy, because such an operation cannot be INPLACE and requires COPY. However, it may be more appropriate in certain circumstances.
Yes, you should remember that you cannot write values ​​to this field either during INSERT or UPDATE - such an operation will lead to an error. So you will have to forget about queries without specifying a list of fields ... well, forget about SELECT * too.

Answer 2

What you want is not the job of the database. This work must be done in the program code prior to saving to the database

Answer 3

What should be the DB Row Type for numbers only?

I assume that they meant "what data type to use for a cell to store numbers in the format 0000000000?". Most likely you will have to use a varchar, since the zeros at the beginning of the string will be stupidly ignored when converted to a number.

but there are numbers in the form (000) -00-00-000

Badly

But I can’t disable or change the number input mask on the site

And it is not necessary, it is not necessary to decide on the forte. This is solved by means of the server language, node, puff, in short BEFORE entering data into the database.