在mysql中，我们可以使用 Substring_Index() 函数以及用户定义的变量。变量帮助我们避免重复某些字符串操作。
架构（mysql v5.7）

CREATE TABLE IF NOT EXISTS `table1` (
  `mainCol` varchar(45) NOT NULL 
) DEFAULT CHARSET=utf8;
INSERT INTO `table1` (`mainCol`) VALUES
  ('1.2.1'),
  ('0.2'),
  ('1'), 
  ('1.2.1-xyz'),
  ('1.2');

查询

SELECT 
  mainCol, 

  @first := SUBSTRING_INDEX(mainCol, '.', 1) AS col1, 

  TRIM(LEADING '.' FROM 
       REPLACE(@second := SUBSTRING_INDEX(mainCol, '.', 2), 
               @first,
               '')
      ) AS col2,  

  @fourth := CASE WHEN INSTR(mainCol, '-') 
                  THEN SUBSTRING_INDEX(mainCol, '-', -1)
                  ELSE ''
             END AS col4, 

  REPLACE(TRIM(LEADING '.' FROM 
               REPLACE(SUBSTRING_INDEX(mainCol, '.', 3), 
                       @second,
                       '')
               ), 
          CONCAT('-', @fourth), 
          '') AS col3 
FROM table1
CROSS JOIN (SELECT @first := '', 
                   @second := '', 
                   @third := '',
                   @fourth := '') AS user_vars;

结果

| mainCol   | col1 | col2 | col4 | col3 |
| --------- | ---- | ---- | ---- | ---- |
| 1.2.1     | 1    | 2    |      | 1    |
| 0.2       | 0    | 2    |      |      |
| 1         | 1    |      |      |      |
| 1.2.1-xyz | 1    | 2    | xyz  | 1    |
| 1.2       | 1    | 2    |      |      |

db fiddle视图